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

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

新規登録して質問してみよう
ただいま回答率
85.44%
SQL Server

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

SQL

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

Q&A

解決済

3回答

14060閲覧

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

NK1994

総合スコア11

SQL Server

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

SQL

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

0グッド

1クリップ

投稿2016/05/11 13:29

編集2016/05/12 01:42

###前提・実現したいこと
はじめまして、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

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

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

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

バッドをするには、ログインかつ

こちらの条件を満たす必要があります。

guest

回答3

0

まず、結合について誤解があるようです。
質問内容の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.の結合がいらないようです。

SQL

1SELECT 2 KYA.地区コード 3 , SUM(k1.買上点数) AS 買上点数1 4 , SUM(k2.買上点数) AS 買上点数2 5 , SUM(k1.買上金額) AS 買上金額1 6 , SUM(k2.買上金額) AS 買上金額2 7FROM 8 ( 9 SELECT 10 KYSDAY.顧客コード, kysday.買上点数, kysday.買上金額 11 FROM 12 KYSDAY 13 WHERE 14 KYSDAY.日付 = '20141215' 15 ) AS k1 16 LEFT JOIN ( 17 SELECT 18 KYSDAY.顧客コード, kysday.買上点数, kysday.買上金額 19 FROM 20 KYSDAY 21 WHERE 22 KYSDAY.日付 = '20150730' 23 ) AS k2 24 ON k1.顧客コード = k2.顧客コード 25 INNER JOIN KYA 26 ON k1.顧客コード = KYA.顧客コード 27GROUP BY 28 KYA.地区コード 29ORDER BY 30 KYA.地区コード

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

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

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

SQL

1SELECT 2 KYSDAY.日付 3 , KYSDAY.顧客コード 4 , KYSDAY.買上点数 5 , KYSDAY.買上金額 6FROM 7 KYSDAY 8WHERE 9 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.次に対応する地区コード列を追加します。

SQL

1SELECT 2 KYA.地区コード 3 , KYSDAY.日付 4 , KYSDAY.顧客コード 5 , KYSDAY.買上点数 6 , KYSDAY.買上金額 7FROM 8 KYSDAY 9 INNER JOIN KYA 10 ON KYA.顧客コード = KYSDAY.顧客コード 11WHERE 12 KYSDAY.日付 IN ('20141215', '20150730') 13ORDER BY 14 KYA.地区コード 15 , KYSDAY.日付 16 , 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.これを集計して、地区ごと、日付ごとの集計値を取得します。

SQL

1SELECT 2 KYA.地区コード 3 , KYSDAY.日付 4 , SUM(KYSDAY.買上点数) AS 買上点数 5 , SUM(KYSDAY.買上金額) AS 買上金額 6FROM 7 KYSDAY 8 INNER JOIN KYA 9 ON KYA.顧客コード = KYSDAY.顧客コード 10WHERE 11 KYSDAY.日付 IN ('20141215', '20150730') 12GROUP BY 13 KYA.地区コード 14 , KYSDAY.日付 15ORDER BY 16 KYA.地区コード 17 , 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を修正して、地区ごとの集計に変更します。

SQL

1SELECT 2 KYA.地区コード 3 , SUM(CASE KYSDAY.日付 WHEN '20141215' THEN KYSDAY.買上点数 END) AS 買上点数1 4 , SUM(CASE KYSDAY.日付 WHEN '20141215' THEN KYSDAY.買上金額 END) AS 買上金額1 5 , SUM(CASE KYSDAY.日付 WHEN '20150730' THEN KYSDAY.買上点数 END) AS 買上点数2 6 , SUM(CASE KYSDAY.日付 WHEN '20150730' THEN KYSDAY.買上金額 END) AS 買上金額2 7FROM 8 KYSDAY 9 INNER JOIN KYA 10 ON KYA.顧客コード = KYSDAY.顧客コード 11WHERE 12 KYSDAY.日付 IN ('20141215', '20150730') 13GROUP BY 14 KYA.地区コード 15ORDER BY 16 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/11 16:52

shiyavako

総合スコア49

バッドをするには、ログインかつ

こちらの条件を満たす必要があります。

NK1994

2016/05/12 00:57

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

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

投稿2016/05/13 00:16

NK1994

総合スコア11

バッドをするには、ログインかつ

こちらの条件を満たす必要があります。

0

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

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

SQL

1SELECT 2 KYA.地区コード 3 , SUM(k1.買上点数) 買上点数1 4 , SUM(k2.買上点数) 買上点数2 5 , SUM(k1.買上金額) 買上金額1 6 , SUM(k2.買上金額) 買上金額2 7FROM KYA 8LEFT JOIN( 9 SELECT 10 顧客コード 11 , SUM(買上点数) 買上点数 12 , SUM(買上金額) 買上金額 13 FROM KYSDAY 14 WHERE 日付 = '20141215' 15 GROUP BY 16 顧客コード 17) k1 18ON k1.顧客コード = KYA.顧客コード 19LEFT JOIN( 20 SELECT 21 顧客コード 22 , SUM(買上点数) 買上点数 23 , SUM(買上金額) 買上金額 24 FROM KYSDAY 25 WHERE 日付 = '20150730' 26 GROUP BY 27 顧客コード 28) k2 29ON k2.顧客コード = KYA.顧客コード 30GROUP BY 31 KYA.地区コード 32ORDER BY 33 KYA.地区コード

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

投稿2016/05/12 02:08

編集2016/05/12 02:13
kutsulog

総合スコア985

バッドをするには、ログインかつ

こちらの条件を満たす必要があります。

NK1994

2016/05/12 02: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
guest

あなたの回答

tips

太字

斜体

打ち消し線

見出し

引用テキストの挿入

コードの挿入

リンクの挿入

リストの挿入

番号リストの挿入

表の挿入

水平線の挿入

プレビュー

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

ただいまの回答率
85.44%

質問をまとめることで
思考を整理して素早く解決

テンプレート機能で
簡単に質問をまとめる

質問する

関連した質問