結合したいテーブルが行数が違うかつ、NULLの入ったテーブルを結合したい

###前提・実現したいこと
sqlserver 初心者です。
今回はタイトルのごとく、結合したいテーブルが行数が違うかつ、NULLの入ったテーブルを結合したいです。

結合するもの↓
グループコード,店舗997の売上売価
NULL 694.00
0001 47162.00
0003 90980.00
0004 61136.00
0005 23659.00
0006 13819.00
0007 19187.00
0008 86637.00
0009 40476.00
0010 30813.00
0011 8831.00
0013 81441.00
0014 67821.00
0015 23294.00
0016 1690.00
0017 23659.00

グループコード,店舗998の売上売価
NULL 498340.00
0003 742.00
0004 22624.00
0005 1838.00
0006 3738.00
0007 3599.00
0008 32522.00
0010 3954.00
0014 5118.00
0015 3078.00
0016 900.00
0017 4800.00

グループコード,店舗999の売上売価
NULL 7880.00
0001 221761.00
0003 311382.00
0004 224474.00
0005 91251.00
0006 39771.00
0007 28180.00
0008 280208.00
0009 8272.00
0010 79309.00
0011 35958.00
0012 10110.00
0013 234175.00
0014 170795.00
0015 7114.00
0016 45018.00
0017 103056.00

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

グループコード,店舗997の売上売価,店舗998の売上売価,店舗999の売上売価
NULL 694.00 498340.00 7880.00
0001 47162.00 NULL 221761.00
0003 90980.00 742.00 311382.00
0004 61136.00 22624.00 224474.00
0005 23659.00 1838.00 91251.00
0006 13819.00 3738.00 39771.00
0007 19187.00 3599.00 28180.00
0008 86637.00 32522.00 280208.00
0009 40476.00 NULL 8272.00
0010 30813.00 3954.00 79309.00
0011 8831.00 NULL 35958.00
0012 NULL NULL 10110.00
0013 81441.00 NULL 234175.00
0014 67821.00 5118.00 170795.00
0015 23294.00 3078.00 7114.00
0016 1690.00 900.00 45018.00
0017 23659.00 4800.00 103056.00

###発生している問題・エラーメッセージ・該当のソースコード
そもそも行数が違うテーブルを結合できるかどうかすらわかりません。
第一にそこが知りたいです。

現状の問題としては、joinの仕方が悪いのか、結合条件がわるいのかで数値がうまく表示されません。
店舗998と999の売上売価がNULLになってしまいます。

問題対象のコード↓
select
GRP.グループコード,
SUM(s1. 売上売価) AS 店舗997の売上売価,
SUM(s2. 売上売価) AS 店舗998の売上売価,
SUM(s3. 売上売価) AS 店舗999の売上売価
from
(select SYODAY.店舗コード , SYODAY.商品コード, sum(SYODAY.売上売価) as 売上売価
from SYODAY

  where 日付 = '20141126' and SYODAY.店舗コード = '997'
  group by SYODAY.店舗コード , SYODAY.商品コード
  
 )as s1 left join SYO on SYO.店舗コード = s1.店舗コード
                     and SYO.商品コード = s1.商品コード

left join(select SYODAY.店舗コード , SYODAY.商品コード, sum(SYODAY.売上売価) as 売上売価
from SYODAY

      where 日付 = '20141126' and SYODAY.店舗コード = '998'
      group by SYODAY.店舗コード , SYODAY.商品コード
      
       ) as s2 on SYO.店舗コード = s2.店舗コード
              and SYO.商品コード = s2.商品コード

left join(select SYODAY.店舗コード , SYODAY.商品コード,sum(SYODAY.売上売価) as 売上売価
from SYODAY

       where 日付 = '20141126' and SYODAY.店舗コード = '999'
       group by SYODAY.店舗コード , SYODAY.商品コード
       
       ) as s3 on SYO.店舗コード = s3.店舗コード
              and SYO.商品コード = s3.商品コード

left join CLS on CLS.店舗コード = SYO.店舗コード
and CLS.クラスコード = SYO.クラスコード
left join BUM on BUM.店舗コード = CLS.店舗コード
and BUM.部門コード = CLS.部門コード
left join GRP on GRP.店舗コード = BUM.店舗コード
and GRP.グループコード = BUM.グループコード

group by GRP.グループコード
order by GRP.グループコード

このコードのようにサブクエリによる結合をしたいですが、うまく表示されません
↓
グループコード,店舗997の売上売価,店舗998の売上売価,店舗999の売上売価
NULL 694.00 NULL NULL
0001 47162.00 NULL NULL
0003 90980.00 NULL NULL
0004 61136.00 NULL NULL
0005 23659.00 NULL NULL
0006 13819.00 NULL NULL
0007 19187.00 NULL NULL
0008 86637.00 NULL NULL
0009 40476.00 NULL NULL
0010 30813.00 NULL NULL
0011 8831.00 NULL NULL
0012 NULL NULL NULL
0013 81441.00 NULL NULL
0014 67821.00 NULL NULL
0015 23294.00 NULL NULL
0016 1690.00 NULL NULL
0017 23659.00 NULL NULL

この結果の店舗998,999のNULLの部分を正しいものを表示させたいです。

###試したこと
上のコードのJOINや結合条件をいろいろいじってみましたがうまくいきません。
店舗コードをばらばらに表示するのはうまくいきます。
どうか皆様のお力をお貸しください。

行動規範の内容に同意します

回答3件

SELECT T1.グループコード
,SUM(T1.店舗997の売上売価) AS 店舗997の売上売価
,SUM(T1.店舗998の売上売価) AS 店舗998の売上売価
,SUM(T1.店舗999の売上売価) AS 店舗999の売上売価
FROM
(SELECT ISNULL(グループコード,'0000') AS グループコード,売上売価 AS 店舗997の売上売価,0 AS 店舗998の売上売価,0 AS 店舗999の売上売価 FROM 店舗997の売上売価
UNION ALL
SELECT ISNULL(グループコード,'0000') AS グループコード,0 AS 売上売価 AS 店舗997の売上売価,売上売価 AS 店舗998の売上売価,0 AS 店舗999の売上売価 FROM 店舗998の売上売価
UNION ALL
SELECT ISNULL(グループコード,'0000') AS グループコード,0 AS 売上売価 AS 店舗997の売上売価,0 AS 店舗998の売上売価,売上売価 AS 店舗999の売上売価 FROM 店舗999の売上売価
) T1
GROUP BY T1.グループコード

投稿2016/05/17 12:32

編集2016/05/17 12:34

markun

総合スコア19

select
s1.グループコード,
SUM(s1. 売上売価) AS 店舗997の売上売価,
SUM(s2. 売上売価) AS 店舗998の売上売価,
SUM(s3. 売上売価) AS 店舗999の売上売価
from
(select GRP.グループコード, SYODAY.店舗コード , sum(SYODAY.売上売価) as 売上売価
from SYODAY
left join SYO on SYO.店舗コード = SYODAY.店舗コード
and SYO.商品コード = SYODAY.商品コード
left join CLS on CLS.店舗コード = SYO.店舗コード
and CLS.クラスコード = SYO.クラスコード
left join BUM on BUM.店舗コード = CLS.店舗コード
and BUM.部門コード = CLS.部門コード
left join GRP on GRP.店舗コード = BUM.店舗コード
and GRP.グループコード = BUM.グループコード

  where 日付 = '20141126' and SYODAY.店舗コード = '997' and not GRP.グループコード = 'NULL'
  group by GRP.グループコード,SYODAY.店舗コード ) as s1

left join(select GRP.グループコード, SYODAY.店舗コード , sum(SYODAY.売上売価) as 売上売価
from SYODAY
left join SYO on SYO.店舗コード = SYODAY.店舗コード
and SYO.商品コード = SYODAY.商品コード
left join CLS on CLS.店舗コード = SYO.店舗コード
and CLS.クラスコード = SYO.クラスコード
left join BUM on BUM.店舗コード = CLS.店舗コード
and BUM.部門コード = CLS.部門コード
left join GRP on GRP.店舗コード = BUM.店舗コード
and GRP.グループコード = BUM.グループコード

  where 日付 = '20141126' and SYODAY.店舗コード = '998' 
  group by GRP.グループコード,SYODAY.店舗コード ) as s2 
  on  s1.グループコード = s2.グループコード

  where 日付 = '20141126' and SYODAY.店舗コード = '999' 
  group by GRP.グループコード,SYODAY.店舗コード ) as s3
  on  s1.グループコード = s3.グループコード

group by s1.グループコード

0001 47162.00 NULL 221761.00
0003 90980.00 742.00 311382.00
0004 61136.00 22624.00 224474.00
0005 23659.00 1838.00 91251.00
0006 13819.00 3738.00 39771.00
0007 19187.00 3599.00 28180.00
0008 86637.00 32522.00 280208.00
0009 40476.00 NULL 8272.00
0010 30813.00 3954.00 79309.00
0011 8831.00 NULL 35958.00
0013 81441.00 NULL 234175.00
0014 67821.00 5118.00 170795.00
0015 23294.00 3078.00 7114.00
0016 1690.00 900.00 45018.00
0017 23659.00 4800.00 103056.00

これで表示したいことができました

投稿2016/05/16 09:45

NK1994

総合スコア11

dekaaki

2016/05/17 03:19

これだと目指したいと書いてあるパターンの0012が抜けてますがそれでよかったということでしょうか？

行動規範の内容に同意します

（書いてある各店舗の売り上げの例と動かないSQLの列名・テーブル名は合わさないと回答する側としてはわかりにくいです。）

とりあえず見た感じ、そもそもメインの表が店舗997にあるデータしか集計できていないように見えます。
また、どこまでをNULLにしたいのかも分からないのでパフォーマンスと見た目は度外視して書いてあるSQLをベースに考えてみました

１．店舗997～999で発生している売り上げのグループコードを一覧化しましょう。

SQL
1select
2	売上グループ.グループコード
3from
4	(
5		select 
6			GRP.グループコード
7		from 
8			SYODAY
9			LEFT OUTER JOIN SYO ON
10				SYO.店舗コード = SYODAY.店舗コード
11				and SYO.商品コード = SYODAY.商品コード  
12			left join CLS on 
13				CLS.店舗コード = SYO.店舗コード
14				and CLS.クラスコード = SYO.クラスコード
15			left join BUM on 
16				BUM.店舗コード = CLS.店舗コード 
17				and BUM.部門コード = CLS.部門コード
18			left join GRP on 
19				GRP.店舗コード = BUM.店舗コード 
20				and GRP.グループコード = BUM.グループコード
21		where
22			日付 = '20141126'
23			AND SYODAY.店舗コード IN ('997', '998', '999')
24		group by
25			GRP.グループコード
26	) 売上グループ

これでほしい回答にあるNULL、0001～0017までの一覧ができます。

２．各店舗の集計(例：店舗997)

SQL
1select 
2	GRP.グループコード
3	,SUM(SYODAY.売上売価) AS 売上売価
4from 
5	SYODAY
6	LEFT OUTER JOIN SYO ON
7		SYO.店舗コード = SYODAY.店舗コード
8		and SYO.商品コード = SYODAY.商品コード  
9	left join CLS on 
10		CLS.店舗コード = SYO.店舗コード
11		and CLS.クラスコード = SYO.クラスコード
12	left join BUM on 
13		BUM.店舗コード = CLS.店舗コード 
14		and BUM.部門コード = CLS.部門コード
15	left join GRP on 
16		GRP.店舗コード = BUM.店舗コード 
17		and GRP.グループコード = BUM.グループコード
18where
19	日付 = '20141126'
20	AND SYODAY.店舗コード = '997'
21group by
22	GRP.グループコード

３．それらを結合

SQL
1select
2	売上グループ.グループコード
3	,店舗997売上.売上売価 AS 店舗997売上売価
4	,店舗998売上.売上売価 AS 店舗998売上売価
5	,店舗999売上.売上売価 AS 店舗999売上売価
6from
7	(
8		select 
9			GRP.グループコード
10		from 
11			SYODAY
12			LEFT OUTER JOIN SYO ON
13				SYO.店舗コード = SYODAY.店舗コード
14				and SYO.商品コード = SYODAY.商品コード  
15			left join CLS on 
16				CLS.店舗コード = SYO.店舗コード
17				and CLS.クラスコード = SYO.クラスコード
18			left join BUM on 
19				BUM.店舗コード = CLS.店舗コード 
20				and BUM.部門コード = CLS.部門コード
21			left join GRP on 
22				GRP.店舗コード = BUM.店舗コード 
23				and GRP.グループコード = BUM.グループコード
24		where
25			日付 = '20141126'
26			AND SYODAY.店舗コード IN ('997', '998', '999')
27		group by
28			GRP.グループコード
29	) 売上グループ
30	left outer join
31		(
32			select 
33				GRP.グループコード
34				,SUM(SYODAY.売上売価) AS 売上売価
35			from 
36				SYODAY
37				LEFT OUTER JOIN SYO ON
38					SYO.店舗コード = SYODAY.店舗コード
39					and SYO.商品コード = SYODAY.商品コード  
40				left join CLS on 
41					CLS.店舗コード = SYO.店舗コード
42					and CLS.クラスコード = SYO.クラスコード
43				left join BUM on 
44					BUM.店舗コード = CLS.店舗コード 
45					and BUM.部門コード = CLS.部門コード
46				left join GRP on 
47					GRP.店舗コード = BUM.店舗コード 
48					and GRP.グループコード = BUM.グループコード
49			where
50				日付 = '20141126'
51				AND SYODAY.店舗コード = '997'
52			group by
53				GRP.グループコード
54		) 店舗997売上 ON 売上グループ.グループコード = 店舗997売上.グループコード
55	left outer join
56		(
57			select 
58				GRP.グループコード
59				,SUM(SYODAY.売上売価) AS 売上売価
60			from 
61				SYODAY
62				LEFT OUTER JOIN SYO ON
63					SYO.店舗コード = SYODAY.店舗コード
64					and SYO.商品コード = SYODAY.商品コード  
65				left join CLS on 
66					CLS.店舗コード = SYO.店舗コード
67					and CLS.クラスコード = SYO.クラスコード
68				left join BUM on 
69					BUM.店舗コード = CLS.店舗コード 
70					and BUM.部門コード = CLS.部門コード
71				left join GRP on 
72					GRP.店舗コード = BUM.店舗コード 
73					and GRP.グループコード = BUM.グループコード
74			where
75				日付 = '20141126'
76				AND SYODAY.店舗コード = '998'
77			group by
78				GRP.グループコード
79		) 店舗998売上 ON 売上グループ.グループコード = 店舗998売上.グループコード
80	left outer join
81		(
82			select 
83				GRP.グループコード
84				,SUM(SYODAY.売上売価) AS 売上売価
85			from 
86				SYODAY
87				LEFT OUTER JOIN SYO ON
88					SYO.店舗コード = SYODAY.店舗コード
89					and SYO.商品コード = SYODAY.商品コード  
90				left join CLS on 
91					CLS.店舗コード = SYO.店舗コード
92					and CLS.クラスコード = SYO.クラスコード
93				left join BUM on 
94					BUM.店舗コード = CLS.店舗コード 
95					and BUM.部門コード = CLS.部門コード
96				left join GRP on 
97					GRP.店舗コード = BUM.店舗コード 
98					and GRP.グループコード = BUM.グループコード
99			where
100				日付 = '20141126'
101				AND SYODAY.店舗コード = '999'
102			group by
103				GRP.グループコード
104		) 店舗999売上 ON 売上グループ.グループコード = 店舗999売上.グループコード

汎用性も何もありませんがとりあえず上記のような感じでどうでしょう？
※テーブル構造や売り上げが無い店舗の部分が0でもいいのであればもう少し整理できます。
また、共通テーブル式も使えるのであれば上記SQLはもう少しシンプルに書けます。

SQL
1WITH 店舗売上 AS (
2		select 
3			GRP.グループコード
4			,SYODAY.店舗コード
5			,SUM(SYODAY.売上売価) AS 売上売価
6		from 
7			SYODAY
8			LEFT OUTER JOIN SYO ON
9				SYO.店舗コード = SYODAY.店舗コード
10				and SYO.商品コード = SYODAY.商品コード  
11			left join CLS on 
12				CLS.店舗コード = SYO.店舗コード
13				and CLS.クラスコード = SYO.クラスコード
14			left join BUM on 
15				BUM.店舗コード = CLS.店舗コード 
16				and BUM.部門コード = CLS.部門コード
17			left join GRP on 
18				GRP.店舗コード = BUM.店舗コード 
19				and GRP.グループコード = BUM.グループコード
20		where
21			日付 = '20141126'
22			AND SYODAY.店舗コード IN ('997', '998', '999')
23		group by
24			GRP.グループコード
25			,SYODAY.店舗コード
26     )
27SELECT
28	売上グループ.グループコード
29FROM
30	(
31		SELECT
32			グループコード
33		FROM
34			店舗売上
35		GROUP BY
36			グループコード
37	) 売上グループ
38	left outer join
39		(
40			select 
41				グループコード
42				,売上売価
43			from 
44				店舗売上
45			where
46				店舗コード = '997'
47		) 店舗997売上 ON 売上グループ.グループコード = 店舗997売上.グループコード
48	left outer join
49		(
50			select 
51				グループコード
52				,売上売価
53			from 
54				店舗売上
55			where
56				店舗コード = '998'
57		) 店舗998売上 ON 売上グループ.グループコード = 店舗998売上.グループコード
58	left outer join
59		(
60			select 
61				グループコード
62				,売上売価
63			from 
64				店舗売上
65			where
66				店舗コード = '999'
67		) 店舗999売上 ON 売上グループ.グループコード = 店舗999売上.グループコード