前提・実現したいこと
はじめまして、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
-
気になる質問をクリップする
クリップした質問は、後からいつでもマイページで確認できます。
またクリップした質問に回答があった際、通知やメールを受け取ることができます。
クリップを取り消します
-
良い質問の評価を上げる
以下のような質問は評価を上げましょう
- 質問内容が明確
- 自分も答えを知りたい
- 質問者以外のユーザにも役立つ
評価が高い質問は、TOPページの「注目」タブのフィードに表示されやすくなります。
質問の評価を上げたことを取り消します
-
評価を下げられる数の上限に達しました
評価を下げることができません
- 1日5回まで評価を下げられます
- 1日に1ユーザに対して2回まで評価を下げられます
質問の評価を下げる
teratailでは下記のような質問を「具体的に困っていることがない質問」、「サイトポリシーに違反する質問」と定義し、推奨していません。
- プログラミングに関係のない質問
- やってほしいことだけを記載した丸投げの質問
- 問題・課題が含まれていない質問
- 意図的に内容が抹消された質問
- 過去に投稿した質問と同じ内容の質問
- 広告と受け取られるような投稿
評価が下がると、TOPページの「アクティブ」「注目」タブのフィードに表示されにくくなります。
質問の評価を下げたことを取り消します
この機能は開放されていません
評価を下げる条件を満たしてません
質問の評価を下げる機能の利用条件
この機能を利用するためには、以下の事項を行う必要があります。
- 質問回答など一定の行動
-
メールアドレスの認証
メールアドレスの認証
-
質問評価に関するヘルプページの閲覧
質問評価に関するヘルプページの閲覧
+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は、極力簡単にすべきであると思います。
投稿
-
回答の評価を上げる
以下のような回答は評価を上げましょう
- 正しい回答
- わかりやすい回答
- ためになる回答
評価が高い回答ほどページの上位に表示されます。
-
回答の評価を下げる
下記のような回答は推奨されていません。
- 間違っている回答
- 質問の回答になっていない投稿
- スパムや攻撃的な表現を用いた投稿
評価を下げる際はその理由を明確に伝え、適切な回答に修正してもらいましょう。
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.地区コード顧客ごとにばらばらのままだと
それぞれの日付に複数の買上があった場合に件数が増えてしまうので
先に顧客ごとにまとめてあげるといいです
投稿
-
回答の評価を上げる
以下のような回答は評価を上げましょう
- 正しい回答
- わかりやすい回答
- ためになる回答
評価が高い回答ほどページの上位に表示されます。
-
回答の評価を下げる
下記のような回答は推奨されていません。
- 間違っている回答
- 質問の回答になっていない投稿
- スパムや攻撃的な表現を用いた投稿
評価を下げる際はその理由を明確に伝え、適切な回答に修正してもらいましょう。
15分調べてもわからないことは、teratailで質問しよう！
- ただいまの回答率 88.10%
- 質問をまとめることで、思考を整理して素早く解決
- テンプレート機能で、簡単に質問をまとめられる
2016/05/12 09:57
私の質問文の言葉足らずですみません。
今回はFROM句内のサブクエリを使用して表示をさせたいです。
なので、CASE文などはまだ勉強してないものとします。
すみません。
試したことの中に表示したい内容を書き加えたので、再度アドバイスよろしくお願いします。