SQL(MySQL5)の質問です。

SQLの書き方についてアドバイスを頂けると助かります。

売上[1] - [*]売上明細[1]-[1]製品[1]-[*]部品構成表[1]-[1]部品

このように繋がっているテーブルがあるとして、1回のクエリで売上、原価を集計することは出来るでしょうか?

売上ID|売上明細ID|製品ID|名前 |売上(明細を集計)|原価(部品単価を集計)
1 |1 |1 |HOGE|100|50
1 |2 |2 |GEHO|200|100
1 |3 |3 |AHO |300|100

製品IDをキーに原価を集計するところ(上記)までは出来たのですが、これをさらに売上IDをキーに集計したいのです。原価についてはうまく集計されるのですが、売上についてはうまく集計できません。

売上[1] - [*]明細(製品)[1] - [*]部品

こんな感じで、1つの製品(売上明細)に複数の部品行が付いているために集計(GROUP BY 売上ID)しようとすると1個の売上明細が部品行の数だけ集計されてしまいます。

・・・とまぁこのような感じで困っています。
面倒くさい質問ではありますが、どうぞよろしくお願いします。

回答の条件
  • 1人5回まで
  • 登録:2008/09/12 10:34:21
  • 終了:2008/09/12 17:14:19

ベストアンサー

id:kn1967 No.1

kn1967回答回数2915ベストアンサー獲得回数3012008/09/12 11:00:52

ポイント250pt

とりあえず見える範囲で考えてみますと

SELECT T1.売上ID,sum(T2.売上) AS 売上集計,sum(T4.単価) AS 部品単価集計
FROM (((売上 AS T1
    LEFT JOIN 売上明細 AS T2 ON T2.売上ID = T1.売上ID)
    LEFT JOIN 部品構成表 AS T3 ON T3.製品ID = T2.製品ID)
    LEFT JOIN 部品 AS T4 ON T4.部品ID = T3.部品ID
GROUP BY T1.売上ID;

それぞれのテーブルがもつカラムの名称が一部不明だったりするので

SQLを投げてもらうほうが楽で正確かと思いますよ。


10万件もの処理となればパフォーマンスMySQLの圧勝だと思いますが

適宜php側での処理を併用することによって、さらなる高速化を期待できたりはします。

id:southgate_01

コメントありがとうございます。


売上明細と製品が「1対1」、製品と部品構成表が「1対多」で繋がっているため、これを一気にSUMしようとすると売上明細の金額が重複されて集計されてしまいます。


まだやってはないですが、このクエリだと同じような結果になりそうな気がするのですが、どうでしょうか?とりあえず取り掛かります。


追記:やってみましたが、やはり部品行分集計されてしまいました。

そうすると、部品行数で割ってやればいんですかね。

2008/09/12 11:29:48

その他の回答(1件)

id:kn1967 No.1

kn1967回答回数2915ベストアンサー獲得回数3012008/09/12 11:00:52ここでベストアンサー

ポイント250pt

とりあえず見える範囲で考えてみますと

SELECT T1.売上ID,sum(T2.売上) AS 売上集計,sum(T4.単価) AS 部品単価集計
FROM (((売上 AS T1
    LEFT JOIN 売上明細 AS T2 ON T2.売上ID = T1.売上ID)
    LEFT JOIN 部品構成表 AS T3 ON T3.製品ID = T2.製品ID)
    LEFT JOIN 部品 AS T4 ON T4.部品ID = T3.部品ID
GROUP BY T1.売上ID;

それぞれのテーブルがもつカラムの名称が一部不明だったりするので

SQLを投げてもらうほうが楽で正確かと思いますよ。


10万件もの処理となればパフォーマンスMySQLの圧勝だと思いますが

適宜php側での処理を併用することによって、さらなる高速化を期待できたりはします。

id:southgate_01

コメントありがとうございます。


売上明細と製品が「1対1」、製品と部品構成表が「1対多」で繋がっているため、これを一気にSUMしようとすると売上明細の金額が重複されて集計されてしまいます。


まだやってはないですが、このクエリだと同じような結果になりそうな気がするのですが、どうでしょうか?とりあえず取り掛かります。


追記:やってみましたが、やはり部品行分集計されてしまいました。

そうすると、部品行数で割ってやればいんですかね。

2008/09/12 11:29:48
id:b-wind No.2

b-wind回答回数3344ベストアンサー獲得回数4402008/09/12 13:18:33

ポイント50pt
SELECT
  製品.ID,売上.ID,SUM(売上明細.金額) AS 売上,
  SUM(
    SELECT 部品.単価 * 部品構成表.数量 * 明細集計.数量
    FROM
        売上明細 AS 明細集計
      LEFT JOIN
        部品構成表 ON 明細集計.製品ID = 部品構成表.製品ID
      LEFT JOIN
        部品 ON 部品構成表.部品ID = 部品.ID
      WHERE
        売上明細.ID = 明細集計.ID
  ) AS 原価
FROM
    売上
  LEFT JOIN
   売上明細 ON 売上.ID = 売上明細.売上ID
  LEFT JOIN
   製品 ON 売上明細.製品ID = 製品.ID
GROUP BY 製品.ID,売上.ID

こんなとこ?

id:southgate_01

ありがとうございます。

どうしてもシンタックスエラーになってしまいます。


SUM(

SELECT 部品.単価 * 部品構成表.数量 * 明細集計.数量

FROM

売上明細 AS 明細集計

LEFT JOIN

部品構成表 ON 明細集計.製品ID = 部品構成表.製品ID

LEFT JOIN

部品 ON 部品構成表.部品ID = 部品.ID

WHERE

売上明細.ID = 明細集計.ID

) AS 原価


ここの部分を削除すると売上高がちゃんと集計されるのですが、この部品単価を集計するところでエラーになります。


もう少し頑張ってみます。

2008/09/12 14:05:30
  • id:southgate_01
    southgate_01 2008/09/12 10:52:47
    追記です。

    残りの集計をプログラム側(PHP)でやるとして、パフォーマンスなどはどうなのでしょうか?例えば処理件数を10万件程度だと見積もったとして、10万件をPHPでループさせるのとMYSQLで集計させるのと、どちらが早いのでしょうか?
  • id:kn1967
    確かに売上明細重複します・・・。

    改良バージョン。
    ただし「売上明細テーブルと製品テーブルを繋ぐ製品ID」と、
    「製品テーブルと部品構成表テーブルを繋ぐ製品ID」は
    同一のものとしているため製品テーブルは引き続き使っていません。

    SELECT T1.売上ID,sum(T2.売上) AS 売上集計
    ,sum(SELECT sum(T4.単価)
    FROM 部品構成表 AS T3
    LEFT JOIN 部品 AS T4 ON T4.部品ID = T3.部品ID
    WHERE T3.製品ID = T2.製品ID
    ) AS 部品単価集計
    FROM 売上 AS T1
    LEFT JOIN 売上明細 AS T2 ON T2.売上ID = T1.売上ID
    GROUP BY T1.売上ID;

    製品テーブルと部品構成表テーブルでVIEW作るべきかな・・・。
  • id:kn1967
    >部品行数で割ってやれば

    コンピュータには常に計算誤差が付きまといますので
    計算量は極力最低限にしたほうがよく
    通常利用の方法としては考えないほうが良いです。
  • id:southgate_01
    southgate_01 2008/09/12 11:37:06
    ありがとうございます。
    早速取り掛かります。
  • id:southgate_01
    southgate_01 2008/09/12 12:07:03
    時間がかかってすいません、ただいま奮闘中です。
    シンタックスエラーで足止めをくらっています。
  • id:southgate_01
    southgate_01 2008/09/12 12:17:50
    SELECT

    SUM(売上明細.金額),

    SUM(

    SELECT SUM((部品.単価 * 部品構成表.数量) * 売上明細.数量)

    FROM

    (((売上 LEFT JOIN 売上明細 ON 売上.ID = 売上明細.売上ID)
    LEFT JOIN 製品 ON 売上明細.製品ID = 製品.ID)
    LEFT JOIN 部品構成表 ON 製品.ID = 部品構成表.製品ID)
    LEFT JOIN 部品 ON 部品構成表.部品ID = 部品.ID

    )

    FROM

    売上 LEFT JOIN 売上明細 ON 売上.ID = 売上明細.売上ID

    GROUP BY 売上.ID

    これがエラーが出るクエリです。
  • id:kn1967
    SELECT
      売上ID,
      SUM(売上明細.金額),
      SUM(SELECT SUM((部品.単価 * 部品構成表.数量) * 売上明細.数量)
        FROM (製品
          LEFT JOIN 部品構成表 ON 製品.ID = 部品構成表.製品ID)
          LEFT JOIN 部品 ON 部品構成表.部品ID = 部品.ID
        WHERE 製品.ID = 売上明細.製品ID
        )
    FROM 売上
      LEFT JOIN 売上明細 ON 売上.ID = 売上明細.売上ID
    GROUP BY 売上.ID;

    ※インデントをつけるため各行の先頭は全角スペースで書いています。
  • id:kn1967
    SELECT
      売上ID,
      SUM(売上明細.金額),
      SUM((SELECT SUM((部品.単価 * 部品構成表.数量) * 売上明細.数量)
        FROM (製品
          LEFT JOIN 部品構成表 ON 製品.ID = 部品構成表.製品ID)
          LEFT JOIN 部品 ON 部品構成表.部品ID = 部品.ID
        WHERE 製品.ID = 売上明細.製品ID
        ))
    FROM 売上
      LEFT JOIN 売上明細 ON 売上.ID = 売上明細.売上ID
    GROUP BY 売上.ID;

    SUM内のサブクエリを一塊として扱わせるために、もう一組(かっこ)が必用でした。
    b-wind 氏の回答でも同じような箇所に(かっこ)を附加してみてください。
  • id:southgate_01
    southgate_01 2008/09/12 17:13:51
    期待通りの結果を得ることが出来ました。
    大変助かりました、ありがとうございました。

この質問への反応(ブックマークコメント)

「あの人に答えてほしい」「この質問はあの人が答えられそう」というときに、回答リクエストを送ってみてましょう。

これ以上回答リクエストを送信することはできません。制限について

絞り込み :
はてなココの「ともだち」を表示します。
回答リクエストを送信したユーザーはいません