MySQLで売り上げ集計表を取得するクエリについて

MySQLで売り上げ集計表を取得するクエリについて質問です。
テーブル構成は次の通りです。

★顧客テーブル
顧客ID
顧客名
（その他カラム省略）

★注文テーブル
注文no（ナンバー）
顧客ID
注文区分（1＝ネット受注、2＝電話受注）
注文日
（その他カラム省略）

★注文詳細テーブル
注文詳細no
注文no
商品ID
数量

★商品テーブル
商品ID
商品名
価格

このようなテーブル構成で、
「特定の日付の顧客一覧および商品の売り上げ数量一覧」
の表を取得したいと考えています。

その日の注文が無い客も含めて、一覧表で取得したいです。

たとえば、今日の売り上げが知りたいとき、どういうクエリを書くべきでしょうか。

以下のようなクエリを使うと、

SELECT a.顧客ID, a.顧客名,
b.注文no, b.注文区分,
c.注文詳細no, c.数量,
d.商品名, d.価格
FROM 顧客テーブル AS a
LEFT JOIN 注文テーブル AS b ON b.顧客ID = a.顧客ID AND b.注文日 = '2015-02-10'
LEFT JOIN 注文詳細テーブル AS c ON c.注文no = b.注文_no
LEFT JOIN 商品テーブル AS d ON d.商品ID = c.商品ID

次のような結果が得られます。

顧客ID 顧客名注文no 注文区分注文詳細no 数量商品名価格
001 海山商事 1 1 1 3 ドライバー 500
002 会社あ NULL NULL NULL NULL NULL NULL NULL
003 会社い NULL NULL NULL NULL NULL NULL NULL
004 三角運送 2 1 2 1 スパナ 300
004 三角運送 2 1 3 1 ドライバー 500
004 三角運送 3 2 4 1 スパナ 300
005 会社う NULL NULL NULL NULL NULL NULL NULL
006 会社え NULL NULL NULL NULL NULL NULL NULL

このとき、三角運送はネット注文と電話注文で、それぞれスパナを１本ずつ注文しているため、
このように行が分かれています。

最終的には以下のような表としてHTML出力したいのですが、
SQL一発では無理でしょうか？
SQLまたはPHPでどのように加工したら望みの結果が得られますでしょうか。

顧客ID 顧客名注文日ドライバースパナ合計金額
001 海山商事 2015-02-10 3 0 1500
002 会社あ NULL NULL NULL NULL
003 会社い NULL NULL NULL NULL
004 三角運送 2015-02-10 1 2 1100

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

回答4件

ベストアンサー

動作確認してないですが↓のようにすれば実現できる気がします。
ドライバー・スパナの数量と売上を顧客ごとに合計する感じです。

lang
1SELECT
2	a.顧客ID,
3	a.顧客名,
4	b.注文日,
5	SUM(CASE WHEN d.商品ID = (ドライバーのID) THEN c.数量 ELSE 0 END) AS 'ドライバー', 
6	SUM(CASE WHEN d.商品ID = (スパナのID) THEN c.数量 ELSE 0 END) AS 'スパナ', 
7	SUM(c.数量 * d.価格) AS 合計金額
8FROM 顧客テーブル AS a 
9	LEFT JOIN 注文テーブル AS b ON b.顧客ID = a.顧客ID AND b.注文日 = '2015-02-10' 
10	LEFT JOIN 注文詳細テーブル AS c ON c.注文no = b.注文_no 
11	LEFT JOIN 商品テーブル AS d ON d.商品ID = c.商品ID
12GROUP BY 顧客ID

投稿2015/02/11 11:57

kinme

総合スコア843

zico_teratail

2015/02/11 12:22

ありがとうございます！テストしてみたところ、これでバッチリ希望通りの結果かもしれません！商品数が少ないうちは、こうやって商品IDを決め打ちでクエリに入れてしまえば、これで解決できそうです。ただ、2015/02/11 18:26投稿でrywさんが指摘されているように、たとえば商品数が膨大だったり動的可変だったりする場合には、このクエリは使えませんよね？そういったときはどうしたら良いか、もし何かアイディアをお持ちでしたらご教授いただけないでしょうか。やっぱりクエリ一発にこだわらず、PHPでゴリゴリ加工するべきか、それともクエリ一発で出来るだけ最終形態に近づけるべきか。クエリ一発のほうが明らかにスマートだしシステムへの負荷も小さいので、可能であればそうしたいのですが・・・。

kinme

2015/02/11 13:02

その時はスクリプト側でクエリの「SUM(CASE～」の部分を商品をループして自動生成するようにして、その自動生成クエリを発行するのがベターだと思います。 SQLのSELECT～FROMのあいだの部分を動的に処理する機能とかは聞いたことないですね。

zico_teratail

2015/02/11 13:09

なるほど、最初に商品テーブルだけにクエリを投げて商品情報を取り出し、それを元に最終的なクエリを組み立てるわけですね。クエリ一発にこだわりすぎて視界が狭くなっておりましたが、その方法ならクエリ二回だけなのでシステム負荷も全く気になりませんね。とても勉強になりました。ありがとうございます。

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

動作検証はできていませんが
今回はMySQL指定ということでMySQL独自拡張のGROUP_CONCATを利用して
以下のようにすることで商品ID決め打ちやアプリケーション側で動的にSQLを生成することなく1回のSQLで処理可能な気がします

lang
1SELECT
2    `a`.`顧客ID`
3  , `a`.`顧客名`
4  , `b`.`注文no`
5  , `b`.`注文区分`
6  , SUM(`c`.`数量` * `d`.`価格`) `合計金額`
7  , GROUP_CONCAT(
8      CONCAT(`d`.`商品名`, '=', `c`.`数量`) SEPARATOR '&'
9    ) `内訳`
10  , `b`.`注文日`
11FROM `顧客テーブル` `a` 
12LEFT JOIN `注文テーブル` `b` ON `b`.`顧客ID` = `a`.`顧客ID` AND `b`.`注文日` = 20150210
13LEFT JOIN 
14  (
15    SELECT
16        SUM(`数量`) `数量`
17      , `注文no`
18      , `商品ID`
19    FROM `注文詳細テーブル`
20    GROUP BY `商品ID`
21  ) `c` ON `c`.`注文no` = `b`.`注文no`
22LEFT JOIN `商品テーブル` `d` ON `d`.`商品ID` = `c`.`商品ID`
23GROUP BY `a`.`顧客ID`
24

内訳は「スパナ=3&ドライバ=1」のようにクエリ文字列のような形式で値が入る（と思う）ので
アプリケーション側で表示の際などに処理を行うといいんじゃないでしょうか。

※ASを省略したり、注文日をINTで与えたりしてるのは、こんなこともできるよ的な意図で必須ではないです

投稿2015/02/12 01:36

編集2015/02/12 07:23

nureha

総合スコア49

zico_teratail

2015/02/12 04:21

ご回答ありがとうございます。ご教示いただいたクエリを投げてみましたところ、エラーとなりました。エラー文は #1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'SEPARATOR `&` ) `utiwake` , `b`.`delivery_date` FROM `cstm` `a` LEFT JOIN' at line 9 です。シンタックス・エラーとのことですが、どこが問題なのか見つけられませんでした。投げたクエリは以下の通りです。 ※質問文では分かりやすいように便宜上、日本語でカラム名などを記載しましたが、実際には下記のようにアルファベットです。 SELECT `a`.`cstm_id` , `a`.`cstm_name` , `b`.`order_no` , `b`.`kubun` , SUM(`c`.`qty` * `d`.`price`) `total_price` , GROUP_CONCAT ( CONCAT(`d`.`item_name`, '=', `c`.`qty`) SEPARATOR `&` ) `utiwake` , `b`.`delivery_date` FROM `cstm` `a` LEFT JOIN `order` `b` ON `b`.`cstm_id` = `a`.`cstm_id` AND `b`.`delivery_date` = 2015-02-10 LEFT JOIN ( SELECT SUM(`qty`) `qty` , `order_no` , `item_no` FROM `order_detail` GROUP BY `item_no` ) `c` ON `c`.`order_no` = `b`.`order_no` LEFT JOIN `item` `d` ON `d`.`item_no` = `c`.`item_no` GROUP BY `a`.`cstm_id`

nureha

2015/02/12 05:02

>SEPARATOR `&` ではなく >SEPARATOR '&' だとどうでしょう。（バッククオート → シングルクオテーション)

zico_teratail

2015/02/12 05:18

シングルクオーテーションも試してみたのですが、やっぱり同じエラーになります。 #1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'SEPARATOR '&' ) `utiwake` , `b`.`delivery_date` FROM `cstm` `a` LEFT JOIN `orde' at line 9

nureha

2015/02/12 05:32

申し訳ないです、見やすくしようとインデントを入れたつもりがさすがにGROUP_CONCATのあとには改行入れちゃうとダメみたいです。。。 >, GROUP_CONCAT >( を >, GROUP_CONCAT( でもう一度お願いします。

zico_teratail

2015/02/12 05:53

ご指示いただいた通りにやってみましたが、やはり結果は同じでした。 #1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '`&`)`utiwake` , `b`.`delivery_date` FROM `cstm` `a` LEFT JOIN `order` `b` ON ' at line 7 何が原因なんでしょう・・・。

nureha

2015/02/12 06:50

またバッククオートになっているようです。両方を同時にしてみてください。

zico_teratail

2015/02/12 07:10

確認不足ですみませんでした！バッククォートとGROUP_CONCATのあとの改行を両方修正したら、エラーが直りました。出てきた結果を見て、なるほど！と思いました。「内訳」の欄にズラズラ・・・っと「&」で結合された商品データが表示されるんですね。あとはこれをPHPなど使って「&区切り」をパースして個別商品ごとに分ければ良いという感じでしょうか。これなら、事前に商品IDを決め打ちする必要も無く、また将来的に商品が増えていってもクエリを変更することなく動的に対応できそうです。とても助かりました。懇切丁寧なご指導をいただき、本当にありがとうございます。 nureha様はもちろんのこと、他の回答者のみなさんもそうですが、こんな神技的なクエリを組み立てられるなんて信じられません！私にとってはもうレベルが高すぎて、思いつきもしない次元でした。職人芸すぎて、私ごときの頭では絶対に自力解決が不可能な問題を助けていただき、感謝しております。

nureha

2015/02/12 07:24

お役に立てたようでよかったです。一応もとの回答も修正しておきました。

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

あーやっぱうまくいかなかったですねｗ

http://www.dbonline.jp/sqlite/select/index14.html
に、「GROUP BY句の後に複数のカラムを指定した場合は、値の組み合わせが同じものでグループ化されます。」とあったので、これの通り顧客IDと注文区分の組み合わせの物をグループ化しちゃえばいいんじゃないかと考えたんです。
ちなみに商品名でグループ化した場合は商品が2個しかないので、結果的に2行（nullを含めると３行）になったかと思ったんですが…なんか勘違いしてますな僕ｗ
nullのグループが出てこない件については調べてるんですけどよくわかりませんでした。後で試してみようと思います。

もしにっちもさっちもいかなくなったら、nullの行を抜き出すクエリ足して、unionすることで回避できるんではとも思いました。

なにはともあれ応援しています。
力になれなくて申し訳ないです！

投稿2015/02/11 11:02

ryw

総合スコア14

zico_teratail

2015/02/11 15:44

ありがとうございます。とても参考になり気付きもありました！

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

調べながら答えてます。あってるかわからないです。間違ってたらすいません。

group by句を使えばうまくいくのでは？？と思いました。
SQLの最後に「group by 顧客ID,注文区分」と入れて、select文に「sum(数量) as 商品ごとの合計数」などと加えてあげると、とりあえずそれに近い形に出来ると考えました。なおその際、「商品ごと合計数」というのが出るわけですから、見た目の都合上select文に「注文区分」も入れてあげる必要があります。

「ドライバー・スパナ」が結果となる表のカラムになっているのはSQLだとつらくなるんじゃないかな…という気がします。phpで頑張るか、できるならUIの設計を変えるべきです。なぜなら、ドライバーやスパナ以外にも商品たくさんあるはずで、指定された日に買われた種類数分、動的にカラムを増やさなくちゃいけなくなるからです。

わかりづらくてごめんなさい！

投稿2015/02/11 09:26

ryw

総合スコア14

zico_teratail

2015/02/11 10:33

ご回答ありがとうございます！とても参考になります。「group by 顧客ID,注文区分」とやってみますと、 004 三角運送 2 1 2 1 スパナ 300 004 三角運送 2 1 3 1 ドライバー 500 004 三角運送 3 2 4 1 スパナ 300 が一つの行にまとまってしまい、ドライバーが結果に出てきません。（004 三角運送 2 1 2 1 スパナ 300 だけになってしまう感じ） GROUP BYを使うとしたら、商品名（商品ID）ではないでしょうか？たとえば以下のように。 SELECT a.顧客ID, a.顧客名, b.注文no, b.注文区分, c.注文詳細no, c.数量, d.商品ID d.商品名, d.価格, SUM(c.`数量`) AS 合計数量 FROM 顧客テーブル AS a LEFT JOIN 注文テーブル AS b ON b.顧客ID = a.顧客ID AND b.注文日 = '2015-02-10' LEFT JOIN 注文詳細テーブル AS c ON c.注文no = b.注文_no LEFT JOIN 商品テーブル AS d ON d.商品ID = c.商品ID GROUP BY 商品ID このようにしますと、「その日に注文がある顧客」については希望どおりの結果が得られるのですが、なぜか「その日に注文が無い顧客」が出てきません。 LEFT JOINしているので、最初の質問本文にも載せたとおり、注文が無い顧客もNULLで結果に出てくるはずなのですが、GROUP BYを追加するとなぜか消えてしまいます。＞ドライバーやスパナ以外にも商品たくさんあるはずで、指定された日に買われた種類数分、動的にカラムを増やさなくちゃいけなくなるこれはたしかにおっしゃる通りなのですが、いまのところ商品数は数種類と少なくて固定です。常に全商品の売り上げ一覧を見たい（要するにエクセルでよくやるような感じで）ので、このような最終形にしたいと考えています。やはり見た目の部分に関しては、phpで頑張って整形するしかなさそうですね。 SQLで得られた結果を配列にでも入れて加工する感じでしょうか。それはともかく、問題なのは前述のように、GROUP BYを使うと途端に「その日の注文が無い顧客」が結果から消えてしまう件です。どうしたら良いでしょうか・・・。

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

あなたの回答