こんにちはゲストさん。会員登録(無料)して質問・回答してみよう!

解決済みの質問

集計表の外部結合

SQL文について勉強中ですが、集計のやり方が分からないためお教えください。

SQL SERVER2008r2

Management StudioでSQLを書いてます。


◎TM部門(店舗マスタ)
店舗CD
店舗名

◎TM店舗部門(店舗部門マスタ)
店舗部門CD
店舗部門名

◎TT売上伝票(売上伝票)
伝票番号

店舗CD
店舗部門CD
売上日
売上時間
商品CD
商品名
数量
税抜単価
税込単価
税抜金額
税込金額
消費税
伝票合計

上記テーブルがあり、日付を限定して集計したいのですが、うまくSQLが書けません。

【希望結果例】
集計期間:2014/04/20 ~ 2014/05/20

日付      | 店舗名 |店舗部門名  |  日別合計
2014/05/21 | 店舗A  | 店舗部門A   | sum(伝票合計)
2014/05/21 | 店舗A  | 店舗部門B   | sum(伝票合計)
2014/05/21 | 店舗A  | 店舗部門C   | sum(伝票合計)
2014/05/21 | 店舗B  | 店舗部門D   | sum(伝票合計)
2014/05/21 | 店舗B  | 店舗部門E   | sum(伝票合計)
2014/05/21 | 店舗B  | 店舗部門F   | sum(伝票合計)
                   ~

SELECT
伝票日付,
店舗CD,
店舗部門CD,
SUM(伝票合計) AS 日別合計
FROM TT売上伝票
GROUP BY 伝票日付,店舗CD,店舗部門CD
HAVING 伝票日付 BETWEEN '2014-04-21' AND '2014-05-20' AND 店舗CD IN(1,2,3,4)
ORDER BY 伝票日付,店舗CD,店舗部門CD

上記の結果
日付      | 店舗CD   |店舗部門CD   |  日別合計
2014/05/21 | 店舗A    | 店舗部門A   | sum(伝票合計)
2014/05/21 | 店舗A    | 店舗部門B   | sum(伝票合計)
2014/05/21 | 店舗A    | 店舗部門C   | sum(伝票合計)
2014/05/21 | 店舗B    | 店舗部門D   | sum(伝票合計)
2014/05/21 | 店舗B    | 店舗部門E   | sum(伝票合計)
2014/05/21 | 店舗B    | 店舗部門F   | sum(伝票合計)


集計は全く問題ないのですが、ここからリレーションし、店舗名、店舗部門名を表示したいのですが、書き方が分かりません。

無知ながら、下記方法を試しました結果
SELECT
TT売上伝票.店舗CD,
TM店舗.店舗名,
TT売上伝票.店舗部門CD,
TM店舗部門.店舗部門名
FROM TT売上伝票
JOINTM店舗
ONTM店舗.店舗CD=TT売上伝票.店舗CD
JOINTM店舗部門
ONTM店舗部門.店舗部門CD=TT売上伝票.店舗部門CD
UNION ALL
SELECT
伝票日付,
店舗CD,
店舗部門CD,
SUM(伝票合計) AS 日別合計
FROM TT売上伝票
GROUP BY 伝票日付,店舗CD,店舗部門CD
HAVING 伝票日付 BETWEEN '2014-04-21' AND '2014-05-20' AND 店舗CD IN(1,2,3,4)
ORDER BY 伝票日付,店舗CD,店舗部門CD

エラーメッセージ
ステートメントが UNION、INTERSECT、EXCEPT のいずれかの演算子を含んでいるときは、選択リスト内に ORDER BY 項目が必要です。

無知ながら、UNION演算子でやる方法と、サブクリエを使う方法があるのではと思いますが、出来れば利点と欠点を含めてお教え頂けたら幸いです。

投稿日時 - 2014-06-16 18:33:02

QNo.8640865

困ってます

質問者が選んだベストアンサー

まず、問題はないというところから。。。

>SELECT
>伝票日付,
>店舗CD,
>店舗部門CD,
>SUM(伝票合計) AS 日別合計
>FROM TT売上伝票
>GROUP BY 伝票日付,店舗CD,店舗部門CD
>HAVING 伝票日付 BETWEEN '2014-04-21' AND '2014-05-20' AND 店舗CD IN(1,2,3,4)
>ORDER BY 伝票日付,店舗CD,店舗部門CD

HAVING句は抽出結果に対して処理されます。
ということで、10年分データがあったら、10年分の全件集計した結果に対して
伝票日付の条件にあうデータを探します。⇒非常に遅い。
普通は、WHERE句を使います。(抽出対象に対して処理されます)
WHERE 伝票日付 BETWEEN '2014-04-21' AND '2014-05-20' AND 店舗CD IN(1,2,3,4)
という感じです。⇒
何年分のデータがあろうが、1か月分を対象として抜き出してから集計されます。
(group by の手前に書いてください)

>リレーションし、店舗名、店舗部門名を表示したいのですが、書き方が分かりません。
Unionについてから。
unionは複数のselectの結果を合わせて出力するときに使います。
設計的には不適切ですが、例えば、1週単位で別のテーブルに持っているなら

SELECT 伝票日付,店舗CD,店舗部門CD,SUM(伝票合計) AS 日別合計
FROM TT売上伝票4月第3週
WHERE 伝票日付 BETWEEN '2014-04-21' AND '2014-05-20' AND 店舗CD IN(1,2,3,4)
GROUP BY 伝票日付,店舗CD,店舗部門CD
union
SELECT 伝票日付,店舗CD,店舗部門CD,SUM(伝票合計) AS 日別合計
FROM TT売上伝票4月第4週
WHERE 伝票日付 BETWEEN '2014-04-21' AND '2014-05-20' AND 店舗CD IN(1,2,3,4)
GROUP BY 伝票日付,店舗CD,店舗部門CD
union
・・・
という風に記述して、4月第3週から5月第2週までのデータをそれぞれのテーブルから取得します。
(例が悪いと思っている理由は、通常は年単位のテーブルや別々の種類のテーブルから
 unionするのが一般的なため。)

で、望みのものは、
SELECT
d.伝票日付,
d.店舗CD,
t.店舗名,
d.店舗部門CD,
b.店舗部門名,
SUM(d.伝票合計) AS 日別合計
FROM TT売上伝票 d
JOIN TM店舗 t
t.店舗CD=d.店舗CD
JOIN TM店舗部門 b
b.店舗部門CD=d.店舗部門CD
where 伝票日付 BETWEEN '2014-04-21' AND '2014-05-20' AND 店舗CD IN(1,2,3,4)
GROUP BY 伝票日付,店舗CD,店舗部門CD
ORDER BY 伝票日付,店舗CD,店舗部門CD

と、結合した結果を集計するか、

SELECT
x.伝票日付,
x.店舗CD,
t.店舗名,
x.店舗部門CD,
b.店舗部門名,
x.日別合計
FROM
(
SELECT 伝票日付,店舗CD,店舗部門CD,SUM(伝票合計) AS 日別合計
FROM TT売上伝票
WHERE 伝票日付 BETWEEN '2014-04-21' AND '2014-05-20' AND 店舗CD IN(1,2,3,4)
GROUP BY 伝票日付,店舗CD,店舗部門CD
) x
JOIN TM店舗 t
t.店舗CD=x.店舗CD
JOIN TM店舗部門 b
b.店舗部門CD=x.店舗部門CD
ORDER BY x.伝票日付,x.店舗CD,x.店舗部門CD

と集計した結果にJOINするかします。

投稿日時 - 2014-06-19 01:54:25

お礼

Siegrune様
詳しい回答ありがとうございます。
まさに、私が知りたかった事でした。

また、GROUP BY(集計)から、抽出の場合は必ず "HAVING"で行うと思っていたので、先に"WHERE"で抽出できるとは考えてもいませんでした。
あわせて、"FROM"以外でも、エイリアスがかけるという事も知りませんでした。

お教えいただいた内容を、よく理解し今後の参考にさせて頂きます。

ありがとうございました。

投稿日時 - 2014-06-19 20:58:05

このQ&Aは役に立ちましたか?

12人が「このQ&Aが役に立った」と投票しています

回答(1)