質問をすることでしか得られない、回答やアドバイスがある。

15分調べてもわからないことは、質問しよう!

ただいまの
回答率

90.50%

  • SQL

    2460questions

    SQL(Structured Query Language)は、リレーショナルデータベース管理システム (RDBMS)のデータベース言語です。大きく分けて、データ定義言語(DDL)、データ操作言語(DML)、データ制御言語(DCL)の3つで構成されており、プログラム上でSQL文を生成して、RDBMSに命令を出し、RDBに必要なデータを格納できます。また、格納したデータを引き出すことも可能です。

  • SQL Server

    602questions

    SQL Serverはマイクロソフトのリレーショナルデータベース管理システムです。データマイニングや多次元解析など、ビジネスインテリジェンスのための機能が備わっています。

FROM句のサブクエリについて

解決済

回答 3

投稿 編集

  • 評価
  • クリップ 1
  • VIEW 4,762

NK1994

score 3

前提・実現したいこと

はじめまして、sqlserver 初心者です。
今サブクエリを勉強しているのですが、FROM句だけは別名をつけてJOINしなければならないので難しいです。
実現したいこととしては、地区コードごとの買取点数と買取金額を日付ごとに表示がしたいです。(FROM句内にサブクエリを使用して)

表示結果としては↓を目指しています。

地区コード,買上点数1,買上点数2,買上金額1,買上金額2
00001    17.00    37.00    1705.00   7538.00
00005    0.00     NULL    0.00       NULL
99999    5.00     NULL    500.00   NULL

発生している問題・エラーメッセージ

現状の問題としては、joinの仕方が悪いのか、結合条件がわるいのかで数値がうまく表示されません。日付ごとに表示したいのに地区コードごとの合計より値が大きくなってしまいます。

地区コードごとの合計↓
select
KYA.地区コード,
SUM(kysday.買上点数) AS 買上点数,
SUM(kysday.買上金額) AS 買上金額
from KYSDAY
inner join KYA on KYSDAY.顧客コード = kya.顧客コード
group by KYA.地区コード
order by KYA.地区コード 

地区コード,買上点数,買上金額
00001    180.00    100110.00
00005    108.00    95482.00
99999    131.00    160980.00

下の日付ごとのコードを入力すると地区ごとより大きな値が出てきてしまいます。

地区コード,買上点数1,買上点数2,買上金額1,買上金額2
00001    4468.00  11926.00 447220.00 2480676.00
00005    0.00      NULL       0.00      NULL
99999    50.00      NULL     5000.00     NULL

この結果を正しいものを表示させたいです。

該当のソースコード

↓2つのコードのどちらかをうまく編集したいです。

select
KYA.地区コード,
SUM(k1.買上点数) AS 買上点数1,
SUM(k2.買上点数) AS 買上点数2,
SUM(k1.買上金額) AS 買上金額1,
SUM(k2.買上金額) AS 買上金額2
from 
(select KYSDAY.顧客コード,kysday.買上点数,kysday.買上金額 from KYSDAY
             where KYSDAY.日付 = '20141215' 
) as k1 
left join (select KYSDAY.顧客コード,kysday.買上点数,kysday.買上金額 from KYSDAY
             where KYSDAY.日付 = '20150730'
) as k2 on k1.顧客コード = k2.顧客コード
inner join KYSDAY on k1.顧客コード = KYSDAY.顧客コード
inner join KYA on KYSDAY.顧客コード = KYA.顧客コード
group by KYA.地区コード
order by KYA.地区コード


select
KYA.地区コード,
SUM(k1.買上点数) AS 買上点数1,
SUM(k2.買上点数) AS 買上点数2,
SUM(k1.買上金額) AS 買上金額1,
SUM(k2.買上金額) AS 買上金額2
from (
(select 顧客コード,買上点数,買上金額 from KYSDAY)as KYSDAY
left join(select KYSDAY.顧客コード,kysday.買上点数,kysday.買上金額 from KYSDAY
             where KYSDAY.日付 = '20141215' ) as k1 on KYSDAY.顧客コード = k1.顧客コード
left join (select KYSDAY.顧客コード,kysday.買上点数,kysday.買上金額 from KYSDAY
             where KYSDAY.日付 = '20150730') as k2 on k1.顧客コード = k2.顧客コード
) as KYSDAY
inner join KYA on KYSDAY.顧客コード = KYA.顧客コード
group by KYA.地区コード
order by KYA.地区コード

試したこと

上のコードのJOINや結合条件をいろいろいじってみましたがうまくいきません。
どうか皆様のお力をお貸しください。
※今回はFROM句内のサブクエリの勉強なのでFROM句内の結合をうまく表示させたいです。

↓の2つの表を平行に表示させる感じにしたいです。
SELECT
  KYA.地区コード
  , sum(KYSDAY.買上点数)as 買上点数
  , sum(KYSDAY.買上金額)as 買上金額 
FROM
  KYSDAY 
  INNER JOIN KYA 
    ON KYA.顧客コード = KYSDAY.顧客コード 
WHERE
  KYSDAY.日付 = '20141215' 
group BY
  KYA.地区コード

地区コード,買上点数,買上金額
00001    17.00    1705.00
00005    0.00    0.00
99999    5.00    500.00

SELECT
  KYA.地区コード
  , sum(KYSDAY.買上点数)as 買上点数
  , sum(KYSDAY.買上金額)as 買上金額 
FROM
  KYSDAY 
  INNER JOIN KYA 
    ON KYA.顧客コード = KYSDAY.顧客コード 
WHERE
  KYSDAY.日付 = '20150730' 
group BY
  KYA.地区コード

地区コード,買上点数,買上金額
00001    37.00    7538.00

表示結果としては↓を目指しています。

地区コード,買上点数1,買上点数2,買上金額1,買上金額2
00001    17.00    37.00    1705.00   7538.00
00005    0.00     NULL    0.00       NULL
99999    5.00     NULL    500.00   NULL

  • 気になる質問をクリップする

    クリップした質問は、後からいつでもマイページで確認できます。

    またクリップした質問に回答があった際、通知やメールを受け取ることができます。

    クリップを取り消します

  • 良い質問の評価を上げる

    以下のような質問は評価を上げましょう

    • 質問内容が明確
    • 自分も答えを知りたい
    • 質問者以外のユーザにも役立つ

    評価が高い質問は、TOPページの「注目」タブのフィードに表示されやすくなります。

    質問の評価を上げたことを取り消します

  • 評価を下げられる数の上限に達しました

    評価を下げることができません

    • 1日5回まで評価を下げられます
    • 1日に1ユーザに対して2回まで評価を下げられます

    質問の評価を下げる

    teratailでは下記のような質問を「具体的に困っていることがない質問」、「サイトポリシーに違反する質問」と定義し、推奨していません。

    • プログラミングに関係のない質問
    • やってほしいことだけを記載した丸投げの質問
    • 問題・課題が含まれていない質問
    • 意図的に内容が抹消された質問
    • 広告と受け取られるような投稿

    評価が下がると、TOPページの「アクティブ」「注目」タブのフィードに表示されにくくなります。

    質問の評価を下げたことを取り消します

    この機能は開放されていません

    評価を下げる条件を満たしてません

    評価を下げる理由を選択してください

    詳細な説明はこちら

    上記に当てはまらず、質問内容が明確になっていない質問には「情報の追加・修正依頼」機能からコメントをしてください。

    質問の評価を下げる機能の利用条件

    この機能を利用するためには、以下の事項を行う必要があります。

回答 3

+1

まず、結合について誤解があるようです。
質問内容のSQLから仮に以下のようなテーブル内容であると仮定します。

KYA
顧客コード,地区コード
A         ,00001
B         ,00001
C         ,00005
D         ,00005
E         ,99999
F         ,99999

KYSDAY
日付    ,顧客コード,買上点数,買上金額
20141215,A         ,   10.00,  100.00
20141215,B         ,   20.00,  200.00
20141215,D         ,   40.00,  400.00
20141215,F         ,   60.00,  600.00
20150730,A         ,  100.00, 1000.00
20150730,B         ,  200.00, 2000.00

1.サブクエリ「k1」の部分で作成されるテーブルは

顧客コード,買上点数,買上金額
A         ,   10.00,  100.00
B         ,   20.00,  200.00
D         ,   40.00,  400.00
F         ,   60.00,  600.00


2.同様にサブクエリ「k2」の部分で作成されるテーブルは

顧客コード,買上点数,買上金額
A         ,  100.00, 1000.00
B         ,  200.00, 2000.00


3.これを顧客コードでLEFT JOIN すると、

顧客コード,k1.買上点数,k1.買上金額,k2.買上点数,k2.買上金額
A         ,      10.00,     100.00,     100.00,    1000.00
B         ,      20.00,     200.00,     200.00,    2000.00
D         ,      40.00,     400.00,       NULL,       NULL
F         ,      60.00,     600.00,       NULL,       NULL


4.KYSDAYと内部結合します。※ここが問題の個所となります。
inner join KYSDAY on k1.顧客コード = KYSDAY.顧客コード 
顧客コード「A」は元のKYSDAYには日付違いで2行あります。(元データが多ければもっと多いでしょう。)
1対多の結合は行が複製されます。

顧客コード,k1.買上点数,k1.買上金額,k2.買上点数,k2.買上金額,KYSDAY.買上点数,KYSDAY.買上金額
A         ,      10.00,     100.00,     100.00,    1000.00,          10.00,         100.00
A         ,      10.00,     100.00,     100.00,    1000.00,         100.00,        1000.00
B         ,      20.00,     200.00,     200.00,    2000.00,          20.00,         200.00
B         ,      20.00,     200.00,     200.00,    2000.00,         200.00,        2000.00
D         ,      40.00,     400.00,       NULL,       NULL,          40.00,         400.00
F         ,      60.00,     600.00,       NULL,       NULL,          60.00,         600.00


5.さらにKYAと内部結合します。

顧客コード,k1.買上点数,k1.買上金額,k2.買上点数,k2.買上金額,KYSDAY.買上点数,KYSDAY.買上金額,地区コード
A         ,      10.00,     100.00,     100.00,    1000.00,          10.00,         100.00,00001
A         ,      10.00,     100.00,     100.00,    1000.00,         100.00,        1000.00,00001
B         ,      20.00,     200.00,     200.00,    2000.00,          20.00,         200.00,00001
B         ,      20.00,     200.00,     200.00,    2000.00,         200.00,        2000.00,00001
D         ,      40.00,     400.00,       NULL,       NULL,          40.00,         400.00,00005
F         ,      60.00,     600.00,       NULL,       NULL,          60.00,         600.00,99999


6.これを地区コードでグループして、k1およびk2の各列の合計を計算します。

地区コード,k1.買上点数,k1.買上金額,k2.買上点数,k2.買上金額
00001     ,      60.00,     600.00,     600.00,    6000.00
00005     ,      40.00,     400.00,       NULL,       NULL
99999     ,      60.00,     600.00,       NULL,       NULL


このようになり、意図しない数値が出来上がります。

さて、上の流れからみると、4.の結合がいらないようです。

SELECT
  KYA.地区コード
  , SUM(k1.買上点数) AS 買上点数1
  , SUM(k2.買上点数) AS 買上点数2
  , SUM(k1.買上金額) AS 買上金額1
  , SUM(k2.買上金額) AS 買上金額2 
FROM
  ( 
    SELECT
      KYSDAY.顧客コード, kysday.買上点数, kysday.買上金額 
    FROM
      KYSDAY 
    WHERE
      KYSDAY.日付 = '20141215'
  ) AS k1 
  LEFT JOIN ( 
    SELECT
      KYSDAY.顧客コード, kysday.買上点数, kysday.買上金額 
    FROM
      KYSDAY 
    WHERE
      KYSDAY.日付 = '20150730'
  ) AS k2 
    ON k1.顧客コード = k2.顧客コード  
  INNER JOIN KYA 
    ON k1.顧客コード = KYA.顧客コード 
GROUP BY
  KYA.地区コード 
ORDER BY
  KYA.地区コード

しかし、これは大きな問題があります。
外部結合は片側のテーブルにあるものすべてと、もう片側から、結合条件が正しいものの一覧を出力します。
そのため、2015/07/30 のほうだけレコードがある場合にはレコードが漏れてしまいます。

また、元のテーブル上で、KYSDAYは、1日当たりの各顧客のレコードが1件として仮定していますが、
複数レコードが存在するような場合には、3.の時点で重複行が表れてきてしまいます。

SQLを考えるときには、まず、対象データの絞り込みから考えます。
1.ほしいデータは、特定の日付のもののみである。

SELECT
  KYSDAY.日付
  , KYSDAY.顧客コード
  , KYSDAY.買上点数
  , KYSDAY.買上金額 
FROM
  KYSDAY 
WHERE
  KYSDAY.日付 IN ('20141215', '20150730')

これをやることで、以下が取得できます。(今回は仮定としたデータにその日付しかありません)

日付    ,顧客コード,買上点数,買上金額
20141215,A         ,   10.00,  100.00
20141215,B         ,   20.00,  200.00
20141215,D         ,   40.00,  400.00
20141215,F         ,   60.00,  600.00
20150730,A         ,  100.00, 1000.00
20150730,B         ,  200.00, 2000.00


2.次に対応する地区コード列を追加します。

SELECT
  KYA.地区コード
  , KYSDAY.日付
  , KYSDAY.顧客コード
  , KYSDAY.買上点数
  , KYSDAY.買上金額 
FROM
  KYSDAY 
  INNER JOIN KYA 
    ON KYA.顧客コード = KYSDAY.顧客コード 
WHERE
  KYSDAY.日付 IN ('20141215', '20150730') 
ORDER BY
  KYA.地区コード
  , KYSDAY.日付
  , KYSDAY.顧客コード

これで集計に必要なデータがそろいました。もう結合するものはありません。

地区コード,日付    ,顧客コード,買上点数,買上金額
00001     ,20141215,A         ,   10.00,  100.00
00001     ,20141215,B         ,   20.00,  200.00
00001     ,20150730,A         ,  100.00, 1000.00
00001     ,20150730,B         ,  200.00, 2000.00
00005     ,20141215,D         ,   40.00,  400.00
99999     ,20141215,F         ,   60.00,  600.00


3.これを集計して、地区ごと、日付ごとの集計値を取得します。

SELECT
  KYA.地区コード
  , KYSDAY.日付
  , SUM(KYSDAY.買上点数) AS 買上点数
  , SUM(KYSDAY.買上金額) AS 買上金額 
FROM
  KYSDAY 
  INNER JOIN KYA 
    ON KYA.顧客コード = KYSDAY.顧客コード 
WHERE
  KYSDAY.日付 IN ('20141215', '20150730') 
GROUP BY
  KYA.地区コード
  , KYSDAY.日付 
ORDER BY
  KYA.地区コード
  , KYSDAY.日付


これで期待する結果に登場するすべての数値が出そろったことになります。

地区コード,日付    ,買上点数,買上金額
00001     ,20141215,   30.00,  300.00
00001     ,20150730,  300.00, 3000.00
00005     ,20141215,   40.00,  400.00
99999     ,20141215,   60.00,  600.00


4.横に並べます。
SQLを修正して、地区ごとの集計に変更します。

SELECT
  KYA.地区コード
  , SUM(CASE KYSDAY.日付 WHEN '20141215' THEN KYSDAY.買上点数 END) AS 買上点数1
  , SUM(CASE KYSDAY.日付 WHEN '20141215' THEN KYSDAY.買上金額 END) AS 買上金額1 
  , SUM(CASE KYSDAY.日付 WHEN '20150730' THEN KYSDAY.買上点数 END) AS 買上点数2
  , SUM(CASE KYSDAY.日付 WHEN '20150730' THEN KYSDAY.買上金額 END) AS 買上金額2 
FROM
  KYSDAY 
  INNER JOIN KYA 
    ON KYA.顧客コード = KYSDAY.顧客コード 
WHERE
  KYSDAY.日付 IN ('20141215', '20150730') 
GROUP BY
  KYA.地区コード
ORDER BY
  KYA.地区コード


これで、期待する結果が得られると思います。

地区コード,買上点数1,買上金額1,買上点数2,買上金額2
00001     ,     30.00,    300.00,    300.00,   3000.00
00005     ,     40.00,    400.00,      NULL,      NULL
99999     ,     60.00,    600.00,      NULL,      NULL

集計関数の中で条件を記述しています。
その条件に合ったものだけを足していくようなイメージです。

比較的昔から使えたと思います。
OVER句などを使ったり、PIVOTなどを使うとより良いのかもしれません。

SQLでは、横に並べるのが非常に弱いです。
列を動的に増やすことはできないので、列名を日付にするようなことは動的にはできません。
Excelにもって行けるものならそのほうがはるかに速かったりします。
Excelから直接SQL Serverも呼べますので、SQL Serverでビューを作成してそれを参照させることもできます。
Excelには、ピボットテーブルの作成機能がありますので、今回のような表も簡単に作成できます。
(しかも横の列は自動的に作成される)
また、グラフや、数式を駆使していろいろな分析もできると思います。

SQLを書いていると、無理すればいろいろなことができますが、書き過ぎると、複雑になりすぎて、修正が大変だったり、
バグの発見が難しくなります。
一つ一つのSQLは、極力簡単にすべきであると思います。

投稿

  • 回答の評価を上げる

    以下のような回答は評価を上げましょう

    • 正しい回答
    • わかりやすい回答
    • ためになる回答

    評価が高い回答ほどページの上位に表示されます。

  • 回答の評価を下げる

    下記のような回答は推奨されていません。

    • 間違っている回答
    • 質問の回答になっていない投稿
    • スパムや攻撃的な表現を用いた投稿

    評価を下げる際はその理由を明確に伝え、適切な回答に修正してもらいましょう。

  • 2016/05/12 09:57

    詳しい解説ありがとうございます。
    私の質問文の言葉足らずですみません。
    今回はFROM句内のサブクエリを使用して表示をさせたいです。
    なので、CASE文などはまだ勉強してないものとします。
    すみません。
    試したことの中に表示したい内容を書き加えたので、再度アドバイスよろしくお願いします。

    キャンセル

check解決した方法

0

select
KYA.地区コード,
SUM(k1.買上点数)  AS 買上点数1,
SUM(k2.買上点数) AS 買上点数2,
SUM(k1.買上金額) AS 買上金額1,
SUM(k2.買上金額) AS 買上金額2
from (
(select KYSDAY.顧客コード,sum(kysday.買上点数) AS 買上点数,sum(kysday.買上金額)AS 買上金額 from KYSDAY
             where KYSDAY.日付 = '20141215'
              group by KYSDAY.顧客コード ) as k1 
             left join KYA
              on k1.顧客コード = KYA.顧客コード

left join (select KYSDAY.顧客コード,sum(kysday.買上点数)AS 買上点数,sum(kysday.買上金額)AS 買上金額 from KYSDAY
             where KYSDAY.日付 = '20150730'
             group by KYSDAY.顧客コード) as k2
              on KYA.顧客コード = k2.顧客コード
)
group by KYA.地区コード
order by KYA.地区コード

これで出したい値を表示することができました。
アドバイスをしてくださった方々、ありがとうございました。

地区コード,買上点数1,買上点数2,買上金額1,買上金額2
00001    17.00    37.00    1705.00    7538.00
00005    0.00    NULL    0.00     NULL
99999    5.00    NULL    500.00     NULL

投稿

  • 回答の評価を上げる

    以下のような回答は評価を上げましょう

    • 正しい回答
    • わかりやすい回答
    • ためになる回答

    評価が高い回答ほどページの上位に表示されます。

  • 回答の評価を下げる

    下記のような回答は推奨されていません。

    • 間違っている回答
    • 質問の回答になっていない投稿
    • スパムや攻撃的な表現を用いた投稿

    評価を下げる際はその理由を明確に伝え、適切な回答に修正してもらいましょう。

0

KYAがマスタで、KYSDAYが実績データ
実績データのうち指定日付を抜き出してまとめたものを横に並べる処理を
FROM句の副問い合わせを使ってやりたいといったところでしょうか?

KYAマスタにそれぞれの日付後のに抜き出したデータを結合してあげればよいかと

SELECT
      KYA.地区コード
    , SUM(k1.買上点数) 買上点数1
    , SUM(k2.買上点数) 買上点数2
    , SUM(k1.買上金額) 買上金額1
    , SUM(k2.買上金額) 買上金額2
FROM KYA
LEFT JOIN(
    SELECT
          顧客コード
        , SUM(買上点数) 買上点数
        , SUM(買上金額) 買上金額
    FROM KYSDAY
    WHERE 日付 = '20141215'
    GROUP BY
          顧客コード
) k1
ON k1.顧客コード = KYA.顧客コード
LEFT JOIN(
    SELECT
          顧客コード
        , SUM(買上点数) 買上点数
        , SUM(買上金額) 買上金額
    FROM KYSDAY
    WHERE 日付 = '20150730'
    GROUP BY
          顧客コード
) k2
ON k2.顧客コード = KYA.顧客コード
GROUP BY
      KYA.地区コード
ORDER BY
      KYA.地区コード

顧客ごとにばらばらのままだと
それぞれの日付に複数の買上があった場合に件数が増えてしまうので
先に顧客ごとにまとめてあげるといいです

投稿

編集

  • 回答の評価を上げる

    以下のような回答は評価を上げましょう

    • 正しい回答
    • わかりやすい回答
    • ためになる回答

    評価が高い回答ほどページの上位に表示されます。

  • 回答の評価を下げる

    下記のような回答は推奨されていません。

    • 間違っている回答
    • 質問の回答になっていない投稿
    • スパムや攻撃的な表現を用いた投稿

    評価を下げる際はその理由を明確に伝え、適切な回答に修正してもらいましょう。

  • 2016/05/12 11:28

    試してみましたが、これではKYAの中に入っている地区コードがすべてでてきてしまうので、求めている結果ではありませんでした。
    00001の値も違いました。

    00001 68.00 206.00 6820.00 41856.00
    00002 NULL NULL NULL NULL
    00005 0.00 NULL 0.00 NULL
    00007 NULL NULL NULL NULL
    00101 NULL NULL NULL NULL
    00102 NULL NULL NULL NULL
    00103 NULL NULL NULL NULL
    00104 NULL NULL NULL NULL
    00105 NULL NULL NULL NULL
    00106 NULL NULL NULL NULL
    00107 NULL NULL NULL NULL
    00108 NULL NULL NULL NULL
    00109 NULL NULL NULL NULL
    00110 NULL NULL NULL NULL
    00201 NULL NULL NULL NULL
    00202 NULL NULL NULL NULL
    00301 NULL NULL NULL NULL
    00302 NULL NULL NULL NULL
    00303 NULL NULL NULL NULL
    00304 NULL NULL NULL NULL
    00305 NULL NULL NULL NULL
    00306 NULL NULL NULL NULL
    00307 NULL NULL NULL NULL
    00308 NULL NULL NULL NULL
    00309 NULL NULL NULL NULL
    00310 NULL NULL NULL NULL
    00401 NULL NULL NULL NULL
    00402 NULL NULL NULL NULL
    00403 NULL NULL NULL NULL
    00413 NULL NULL NULL NULL
    00501 NULL NULL NULL NULL
    00502 NULL NULL NULL NULL
    00503 NULL NULL NULL NULL
    00504 NULL NULL NULL NULL
    00505 NULL NULL NULL NULL
    00506 NULL NULL NULL NULL
    00601 NULL NULL NULL NULL
    00701 NULL NULL NULL NULL
    00784 NULL NULL NULL NULL
    00801 NULL NULL NULL NULL
    00901 NULL NULL NULL NULL
    00999 NULL NULL NULL NULL
    01001 NULL NULL NULL NULL
    01101 NULL NULL NULL NULL
    09901 NULL NULL NULL NULL
    09999 NULL NULL NULL NULL
    99999 5.00 NULL 500.00 NULL

    キャンセル

15分調べてもわからないことは、teratailで質問しよう!

  • ただいまの回答率 90.50%
  • 質問をまとめることで、思考を整理して素早く解決
  • テンプレート機能で、簡単に質問をまとめられる

関連した質問

同じタグがついた質問を見る

  • SQL

    2460questions

    SQL(Structured Query Language)は、リレーショナルデータベース管理システム (RDBMS)のデータベース言語です。大きく分けて、データ定義言語(DDL)、データ操作言語(DML)、データ制御言語(DCL)の3つで構成されており、プログラム上でSQL文を生成して、RDBMSに命令を出し、RDBに必要なデータを格納できます。また、格納したデータを引き出すことも可能です。

  • SQL Server

    602questions

    SQL Serverはマイクロソフトのリレーショナルデータベース管理システムです。データマイニングや多次元解析など、ビジネスインテリジェンスのための機能が備わっています。