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

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

ただいまの
回答率

88.03%

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

解決済

回答 4

投稿 編集

  • 評価
  • クリップ 2
  • VIEW 7,166

score 467

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


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

最終的には以下のような表として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
  • 気になる質問をクリップする

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

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

    クリップを取り消します

  • 良い質問の評価を上げる

    以下のような質問は評価を上げましょう

    • 質問内容が明確
    • 自分も答えを知りたい
    • 質問者以外のユーザにも役立つ

    評価が高い質問は、TOPページの「注目」タブのフィードに表示されやすくなります。

    質問の評価を上げたことを取り消します

  • 評価を下げられる数の上限に達しました

    評価を下げることができません

    • 1日5回まで評価を下げられます
    • 1日に1ユーザに対して2回まで評価を下げられます

    質問の評価を下げる

    teratailでは下記のような質問を「具体的に困っていることがない質問」、「サイトポリシーに違反する質問」と定義し、推奨していません。

    • プログラミングに関係のない質問
    • やってほしいことだけを記載した丸投げの質問
    • 問題・課題が含まれていない質問
    • 意図的に内容が抹消された質問
    • 過去に投稿した質問と同じ内容の質問
    • 広告と受け取られるような投稿

    評価が下がると、TOPページの「アクティブ」「注目」タブのフィードに表示されにくくなります。

    質問の評価を下げたことを取り消します

    この機能は開放されていません

    評価を下げる条件を満たしてません

    評価を下げる理由を選択してください

    詳細な説明はこちら

    上記に当てはまらず、質問内容が明確になっていない質問には「情報の追加・修正依頼」機能からコメントをしてください。

    質問の評価を下げる機能の利用条件

    この機能を利用するためには、以下の事項を行う必要があります。

回答 4

checkベストアンサー

+3

動作確認してないですが↓のようにすれば実現できる気がします。
ドライバー・スパナの数量と売上を顧客ごとに合計する感じです。
SELECT
    a.顧客ID,
    a.顧客名,
    b.注文日,
    SUM(CASE WHEN d.商品ID = (ドライバーのID) THEN c.数量 ELSE 0 END) AS 'ドライバー', 
    SUM(CASE WHEN d.商品ID = (スパナのID) THEN c.数量 ELSE 0 END) AS 'スパナ', 
    SUM(c.数量 * d.価格) 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

投稿

  • 回答の評価を上げる

    以下のような回答は評価を上げましょう

    • 正しい回答
    • わかりやすい回答
    • ためになる回答

    評価が高い回答ほどページの上位に表示されます。

  • 回答の評価を下げる

    下記のような回答は推奨されていません。

    • 間違っている回答
    • 質問の回答になっていない投稿
    • スパムや攻撃的な表現を用いた投稿

    評価を下げる際はその理由を明確に伝え、適切な回答に修正してもらいましょう。

  • 2015/02/11 21:22

    ありがとうございます!
    テストしてみたところ、これでバッチリ希望通りの結果かもしれません!

    商品数が少ないうちは、こうやって商品IDを決め打ちでクエリに入れてしまえば、これで解決できそうです。

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

    キャンセル

  • 2015/02/11 22:02

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

    キャンセル

  • 2015/02/11 22:09

    なるほど、最初に商品テーブルだけにクエリを投げて商品情報を取り出し、それを元に最終的なクエリを組み立てるわけですね。

    クエリ一発にこだわりすぎて視界が狭くなっておりましたが、その方法ならクエリ二回だけなのでシステム負荷も全く気になりませんね。

    とても勉強になりました。
    ありがとうございます。

    キャンセル

+2

動作検証はできていませんが
今回はMySQL指定ということでMySQL独自拡張のGROUP_CONCATを利用して
以下のようにすることで商品ID決め打ちやアプリケーション側で動的にSQLを生成することなく1回のSQLで処理可能な気がします
SELECT
    `a`.`顧客ID`
  , `a`.`顧客名`
  , `b`.`注文no`
  , `b`.`注文区分`
  , SUM(`c`.`数量` * `d`.`価格`) `合計金額`
  , GROUP_CONCAT(
      CONCAT(`d`.`商品名`, '=', `c`.`数量`) SEPARATOR '&'
    ) `内訳`
  , `b`.`注文日`
FROM `顧客テーブル` `a` 
LEFT JOIN `注文テーブル` `b` ON `b`.`顧客ID` = `a`.`顧客ID` AND `b`.`注文日` = 20150210
LEFT JOIN 
  (
    SELECT
        SUM(`数量`) `数量`
      , `注文no`
      , `商品ID`
    FROM `注文詳細テーブル`
    GROUP BY `商品ID`
  ) `c` ON `c`.`注文no` = `b`.`注文no`
LEFT JOIN `商品テーブル` `d` ON `d`.`商品ID` = `c`.`商品ID`
GROUP BY `a`.`顧客ID`
内訳は「スパナ=3&ドライバ=1」のようにクエリ文字列のような形式で値が入る(と思う)ので
アプリケーション側で表示の際などに処理を行うといいんじゃないでしょうか。

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

投稿

編集

  • 回答の評価を上げる

    以下のような回答は評価を上げましょう

    • 正しい回答
    • わかりやすい回答
    • ためになる回答

    評価が高い回答ほどページの上位に表示されます。

  • 回答の評価を下げる

    下記のような回答は推奨されていません。

    • 間違っている回答
    • 質問の回答になっていない投稿
    • スパムや攻撃的な表現を用いた投稿

    評価を下げる際はその理由を明確に伝え、適切な回答に修正してもらいましょう。

  • 2015/02/12 15:50

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

    キャンセル

  • 2015/02/12 16:10

    確認不足ですみませんでした!
    バッククォートとGROUP_CONCATのあとの改行を両方修正したら、エラーが直りました。

    出てきた結果を見て、なるほど!と思いました。

    「内訳」の欄にズラズラ・・・っと「&」で結合された商品データが表示されるんですね。
    あとはこれをPHPなど使って「&区切り」をパースして個別商品ごとに分ければ良いという感じでしょうか。

    これなら、事前に商品IDを決め打ちする必要も無く、また将来的に商品が増えていってもクエリを変更することなく動的に対応できそうです。

    とても助かりました。
    懇切丁寧なご指導をいただき、本当にありがとうございます。

    nureha様はもちろんのこと、他の回答者のみなさんもそうですが、こんな神技的なクエリを組み立てられるなんて信じられません!
    私にとってはもうレベルが高すぎて、思いつきもしない次元でした。
    職人芸すぎて、私ごときの頭では絶対に自力解決が不可能な問題を助けていただき、感謝しております。

    キャンセル

  • 2015/02/12 16:24

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

    キャンセル

+1

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

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

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

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

投稿

  • 回答の評価を上げる

    以下のような回答は評価を上げましょう

    • 正しい回答
    • わかりやすい回答
    • ためになる回答

    評価が高い回答ほどページの上位に表示されます。

  • 回答の評価を下げる

    下記のような回答は推奨されていません。

    • 間違っている回答
    • 質問の回答になっていない投稿
    • スパムや攻撃的な表現を用いた投稿

    評価を下げる際はその理由を明確に伝え、適切な回答に修正してもらいましょう。

  • 2015/02/11 19: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を使うと途端に「その日の注文が無い顧客」が結果から消えてしまう件です。
    どうしたら良いでしょうか・・・。

    キャンセル

+1

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

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

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

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

投稿

  • 回答の評価を上げる

    以下のような回答は評価を上げましょう

    • 正しい回答
    • わかりやすい回答
    • ためになる回答

    評価が高い回答ほどページの上位に表示されます。

  • 回答の評価を下げる

    下記のような回答は推奨されていません。

    • 間違っている回答
    • 質問の回答になっていない投稿
    • スパムや攻撃的な表現を用いた投稿

    評価を下げる際はその理由を明確に伝え、適切な回答に修正してもらいましょう。

  • 2015/02/12 00:44

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

    キャンセル

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

  • ただいまの回答率 88.03%
  • 質問をまとめることで、思考を整理して素早く解決
  • テンプレート機能で、簡単に質問をまとめられる

関連した質問

同じタグがついた質問を見る