SQL(MySQL5)の質問です。

Question

southgate_01

61

61もっと見る

300pt

コンピュータウェブ制作

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

※ 有料アンケート・ポイント付き質問機能は2023年2月28日に終了しました。

No.2

b-wind33444402008/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

こんなとこ？

ありがとうございます。

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

SUM(

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

FROM

売上明細 AS 明細集計

LEFT JOIN

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

LEFT JOIN

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

WHERE

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

) AS 原価

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

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

2008/09/12 14:05:30

southgate_01 2008/09/12 10:52:47

追記です。

残りの集計をプログラム側(PHP)でやるとして、パフォーマンスなどはどうなのでしょうか?例えば処理件数を10万件程度だと見積もったとして、10万件をPHPでループさせるのとMYSQLで集計させるのと、どちらが早いのでしょうか?
kn1967 2008/09/12 11:30:12

確かに売上明細重複します・・・。

改良バージョン。
ただし「売上明細テーブルと製品テーブルを繋ぐ製品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作るべきかな・・・。
kn1967 2008/09/12 11:36:23

＞部品行数で割ってやれば

コンピュータには常に計算誤差が付きまといますので
計算量は極力最低限にしたほうがよく
通常利用の方法としては考えないほうが良いです。
southgate_01 2008/09/12 11:37:06

ありがとうございます。
早速取り掛かります。
southgate_01 2008/09/12 12:07:03

時間がかかってすいません、ただいま奮闘中です。
シンタックスエラーで足止めをくらっています。
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

これがエラーが出るクエリです。
kn1967 2008/09/12 13:11:13

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;

※インデントをつけるため各行の先頭は全角スペースで書いています。
kn1967 2008/09/12 15:08:57

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 氏の回答でも同じような箇所に（かっこ）を附加してみてください。
southgate_01 2008/09/12 17:13:51

期待通りの結果を得ることが出来ました。
大変助かりました、ありがとうございました。

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

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

リクエスト送信済

回答リクエストを送信したユーザーはいません

kn1967 · Accepted Answer · 2008-09-12T11:00:52+09:00

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

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側での処理を併用することによって、さらなる高速化を期待できたりはします。

kn1967 · Accepted Answer · 2008-09-12T11:00:52+09:00

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

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側での処理を併用することによって、さらなる高速化を期待できたりはします。

SQL(MySQL5)の質問です。

ベストアンサー

kn196729153012008/09/12 11:00:52

その他の回答（1件）

kn196729153012008/09/12 11:00:52ここでベストアンサー

b-wind33444402008/09/12 13:18:33

コメント（9件)

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