SQL文 グループ集計TOPに対する明細

質問

すみません、教えてください。
得意先の売上額上位10社のそれぞれについて、仕入合計額上位10商品のリストを出力するSQL
文をがわからなくて困っています。

できれば、一般的なSQL文(SQL92)でお願いします。

テーブルは、下記の2つです。
--------------------------------------------
・t_売上明細
　売上日
　得意先コード
　商品コード
　売上金額

・t_商品マスタ
　商品コード
　仕入単価

<出力>======================================
得意先コード, 売上合計,商品コード,仕入額合計
--------------------------------------------
C3 \300100
　　　A08 \32500
　　　 A04 \31000
　　　 ～～～
　　　A10 \30000(Top10)
C2 \200200
　　　A05 \22500
　　　A04 \21000
　　　 ～～～
　　　 A10 \20000(Top10)
～～～ ～～～
C10 \1000(Top10)
　　　A97 \500
　　　A14 \100
　　　～～～
　　　A96 \10(Top10)
================================

回答No.3

root139

> ちなみに、SQL99ですと、もう少しスマートになるのでしょうか？

SQL99 だと WITH 句を使って整理すれば、少しはマシになるかも知れません。
SQL2003 のウィンドウ関数や Oracle の rownum を使えば、かなりスッキリしそうです。

http://ja.wikipedia.org/wiki/%E3%82%A6%E3%82%A3%E3%83%B3%E3%83%89%E3%82%A6%E9%96%A2%E6%95%B0
http://docs.oracle.com/cd/E16338_01/server.112/b56299/pseudocolumns009.htm

回答No.2

root139

下記の前提で考えました。

・t_売上明細には売上個数のカラムも存在する
・どのテーブルのどの項目にも null は存在しない
・売上日に関する指定は無いので全期間の合計

SQL92しばりということなので、かなりややこしく、また、おそらく相当非効率になってしまいましたが、出来ることは出来ると思われます。

----------------------------------------
SELECT a.得意先コード, 売上合計, 商品コード, 仕入額合計 FROM

(
SELECT 得意先コード, SUM(売上金額) AS 売上合計
FROM t_売上明細 GROUP BY 得意先コード
ORDER BY SUM(売上金額) DESC LIMIT 10
) a

INNER JOIN

(
SELECT 得意先コード, 商品コード, SUM(売上個数 * 仕入単価) AS 仕入額合計
FROM t_売上明細 b INNER JOIN t_商品マスタ c USING(商品コード)
GROUP BY 得意先コード, 商品コード
HAVING
(
SELECT COUNT(*) FROM
(
SELECT 商品コード
FROM t_売上明細 d INNER JOIN t_商品マスタ USING(商品コード)
WHERE b.得意先コード = d.得意先コード
GROUP BY 商品コード
HAVING SUM(売上個数 * 仕入単価) > SUM(b.売上個数 * c.仕入単価)
) e
) < 10
) f

USING(得意先コード)

ORDER BY 売上合計 DESC, 得意先コード, 仕入額合計 DESC, 商品コード;
----------------------------------------

最初のサブクエリ(a)は売上合計の取得とその上位10社への絞込み、次のサブクエリ(f)は仕入額合計の取得とその得意先毎の上位10商品への絞込みを行なっています。

なお、売上合計が同じ得意先が有って10位が複数有る場合などは、その内どれか一つしか抽出されません。その場合、最初のサブクエリ(a)もHAVINGを使って絞り込む様にすれば、全て抽出されるようになります。

参考URL：

http://codezine.jp/article/detail/460

お礼

ありがとうございます。
方向性はなんとなくわかりました。
ちなみに、SQL99ですと、もう少しスマートになるのでしょうか？

回答No.1

root139

どこかに商品の個数データが無い限り、仕入額合計を出すのは無理かと・・・。

お礼

御返答 ありがとうございます。
すみません、テーブルは 簡易的に書き出したもので 実際とは多少異なります。

確かに 売上個数が必要ですね。
付け加えての 考慮をお願いします。

私が不明なポイントは、ＴＯＰ１０の それぞれに対しての ＴＯＰ１０のひっぱり方です。