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

Question

###前提・実現したいこと
はじめまして、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句内の結合をうまく表示させたいです。

↓の２つの表を平行に表示させる感じにしたいです。
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

Answer 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

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

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

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

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

３．これを顧客コードで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

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

顧客コード,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

５．さらに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

６．これを地区コードでグループして、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

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

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

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は、１日当たりの各顧客のレコードが１件として仮定していますが、
複数レコードが存在するような場合には、３．の時点で重複行が表れてきてしまいます。

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

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

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

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

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

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

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

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

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

地区コード,買上点数１,買上金額１,買上点数２,買上金額２
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は、極力簡単にすべきであると思います。

Comments

[NK1994]

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

Answer 2

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

Answer 3

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.地区コード

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

Comments

[NK1994]

試してみましたが、これでは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