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

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

新規登録して質問してみよう
ただいま回答率
85.50%
MySQL

MySQL(マイエスキューエル)は、TCX DataKonsultAB社などが開発するRDBMS(リレーショナルデータベースの管理システム)です。世界で最も人気の高いシステムで、オープンソースで開発されています。MySQLデータベースサーバは、高速性と信頼性があり、Linux、UNIX、Windowsなどの複数のプラットフォームで動作することができます。

Q&A

解決済

2回答

1550閲覧

MySQLで複数のテーブルに対して集計関数を使用したい

iorin

総合スコア32

MySQL

MySQL(マイエスキューエル)は、TCX DataKonsultAB社などが開発するRDBMS(リレーショナルデータベースの管理システム)です。世界で最も人気の高いシステムで、オープンソースで開発されています。MySQLデータベースサーバは、高速性と信頼性があり、Linux、UNIX、Windowsなどの複数のプラットフォームで動作することができます。

0グッド

0クリップ

投稿2018/05/15 04:40

編集2018/05/15 07:46

集計関数を複数のテーブルで行いたいです。

以下のような4つのテーブルが存在します。

TBL1

RH_PKRD_PKC_CdO_NoItem_No
104101A1001
104201A1002
104301A1003
104401A1004

TBL2

RH_PKRD_PKS_PKC_CdItem_NoQuantity
104110111
104120110
104210121
104220121
104310130
104320132
104410141
104420143

TBL3(TBL1とTBL4の紐付けに必要)

RH_PKR_PKC_CdO_No
10101A100

TBL4

R_PKD_PKC_CdItem_NoDiff
1310111
1320121
1330132
1340142

今回欲しい結果
※QuantityとDiffはレコード増える仕様
この例ではDiffは1つですが複数行ある場合を仮定して頂きたいです。

RH_PKRD_PKItem_NoSUM(SD.Quantity)SUM(RRD.Diff)
1041111
1042221
1043322
1044442

それぞれ、別のSQLであれば欲しいデータが取得することは出来ました。

1つ目

SELECT RD.Item_No , RD.RD_PK , RD.RD_PK , SUM(SD.Quantity) FROM TBL1 RD INNER JOIN TBL2 SD ON RD.C_Cd = SD.C_Cd AND RD.RH_PK = SD.RH_PK WHERE RD.C_Cd = '01' AND RD.O_No = 'A100' AND RD.Item_No = SD.Item_No GROUP BY SD.Item_No

2つ目

SELECT SUM(RRD.Diff) FROM TBL4 RRD INNER JOIN TBL3 RRH ON RRH.C_Cd = RRD.C_Cd AND RRH.R_PK = RRD.R_PK WHERE RRH.C_Cd = '01' AND RRH.O_No = 'A100' GROUP BY RRD.Diff , RRD.Item_No

この2つを合体させてみたのが以下です。

・SELECT句にサブクエリを作成
このSQLだとサブクエリの結果が1件以上というエラーが返ってきてしまい実行できませんでした。

SELECT ( SELECT SUM(RRD.Diff) FROM TBL4 RRD INNER JOIN TBL3 RRH ON RRH.C_Cd = RRD.C_Cd AND RRH.R_PK = RRD.R_PK WHERE RRH.C_Cd = '01' AND RRH.O_No = 'A100' GROUP BY RRD.Diff , RRD.Item_No ) as Diff , RD.RH_PK , RD.RD_PK , RD.Item_No , SUM(SD.Quantity) FROM TBL1 RD INNER JOIN TBL2 SD ON RD.C_Cd = SD.C_Cd AND RD.RH_PK = SD.RH_PK INNER JOIN TBL3 RRH ON RRH.C_Cd = RD.C_Cd AND RRH.O_No = RD.O_No INNER JOIN TBL4 RRD ON RRD.C_Cd = RRH.C_Cd AND RRH.R_PK = RRD.R_PK WHERE RD.C_Cd = '01' AND RD.O_No = 'A100' AND RD.Item_No = SD.Item_No AND RD.Item_No = RRD.Item_No GROUP BY SD.Item_No

Subquery returns more than 1 row

・WHERE句にサブクエリを入れてみました。
これだとまず、Diffを取得するための記述方法がわかりませんでした。
また、このSQLだとQuantityの値がなぜか4倍になってしまいました。。

SELECT RD.Item_No , SUM(SD.Quantity) , RD.RH_PK , RD.RD_PK FROM TBL1 RD INNER JOIN TBL2 SD ON RD.C_Cd = SD.C_Cd AND RD.RH_PK = SD.RH_PK LEFT JOIN ( SELECT SUM(Diff) FROM TBL4 RRD INNER JOIN TBL3 RRH ON RRH.C_Cd = '01' AND RRH.R_PK = RRD.R_PK WHERE RRH.C_Cd = '01' AND RRH.O_No = 'A100' GROUP BY Item_No ) as Diff ON RD.C_Cd = RD.C_Cd WHERE RD.C_Cd = '01' AND RD.O_No = 'A100' AND RD.Item_No = SD.Item_No AND RD.RH_PK = SD.RH_PK GROUP BY SD.Item_No

別々で取得してPHP側で配列操作することは可能なんですが、
出来れば1つのSQLで取得したいと考えています。

↓追記↓
CREATE文

CREATE TABLE `TBL1` ( `RH_PK` int (11) NOT NULL DEFAULT '0' , `RD_PK` int (11) NOT NULL AUTO_INCREMENT , `C_Cd` varchar (4) NOT NULL , `O_No` varchar (10) NOT NULL , `Item_No` int (11) NOT NULL , PRIMARY KEY (`RD_PK`) , UNIQUE KEY `C_Cd` (`C_Cd`, `O_No`, `Item_No`) ) ENGINE = InnoDB DEFAULT CHARSET = utf8; CREATE TABLE `TBL2` ( `RH_PK` int (11) NOT NULL , `RD_PK` int (11) NOT NULL , `S_PK` int (11) NOT NULL , `C_Cd` varchar (4) NOT NULL , `Item_No` int (11) NOT NULL , `Quantity` decimal (5, 0) DEFAULT NULL , PRIMARY KEY (`RH_PK`, `RD_PK`, `S_PK`) ) ENGINE = InnoDB DEFAULT CHARSET = utf8; CREATE TABLE `TBL3` ( `R_PK` int (11) NOT NULL AUTO_INCREMENT , `C_Cd` varchar (4) NOT NULL , `RH_PK` int (11) NOT NULL , `O_No` varchar (10) NOT NULL , PRIMARY KEY (`R_PK`) ) ENGINE = InnoDB DEFAULT CHARSET = utf8; CREATE TABLE `TBL4` ( `D_PK` int (11) NOT NULL AUTO_INCREMENT , `R_PK` int (11) NOT NULL , `C_Cd` varchar (4) NOT NULL , `Item_No` int (11) NOT NULL , `Diff` decimal (5, 0) DEFAULT NULL , PRIMARY KEY (`D_PK`) ) ENGINE = InnoDB DEFAULT CHARSET = utf8;

INSERT文

INSERT INTO TBL1(RH_PK, RD_PK, C_Cd, O_No, Item_No) values (10, 41, 01, 'A100', 1) , (10, 42, 01, 'A100', 2) , (10, 43, 01, 'A100', 3) , (10, 44, 01, 'A100', 4); INSERT INTO TBL2(RH_PK, RD_PK, S_PK, C_Cd, Item_No, Quantity) values (10, 41, 1, 01, 1, 1) , (10, 41, 2, 01, 1, 0) , (10, 42, 1, 01, 2, 1) , (10, 42, 2, 01, 2, 1) , (10, 43, 1, 01, 3, 0) , (10, 43, 2, 01, 3, 2) , (10, 44, 1, 01, 4, 1) , (10, 44, 2, 01, 4, 3); INSERT INTO TBL3(RH_PK, R_PK, C_Cd, O_No) values (10, 1, 01, 'A100'); INSERT INTO TBL4(R_PK, D_PK, C_Cd, Item_No, Diff) values (1, 31, 01, 1, 1) , (1, 32, 01, 2, 1) , (1, 33, 01, 3, 2) , (1, 34, 01, 4, 2);

↑追記↑

↓追記2↓

TBL4(追記)

R_PKD_PKC_CdItem_NoDiff
1310111
1320121
1330132
1340142
1350111
1360122
1370130
1380141

追記後の取得したい結果

RH_PKRD_PKItem_NoSUM(SD.Quantity)SUM(RRD.Diff)
1041112
1042223
1043322
1044443

↑追記2↑

何か良い方法があれば教えて頂きたいです。
よろしくお願いします。

気になる質問をクリップする

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

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

バッドをするには、ログインかつ

こちらの条件を満たす必要があります。

m.ts10806

2018/05/15 04:42

データ部分を記号で囲われていますが、マークダウンにはテーブルの機能があるのでそちらを使われた方が見やすくなりますよ。ご活用ください。
iorin

2018/05/15 05:04

アドバイスありがとうございます。編集しました。
yambejp

2018/05/15 06:45

データがおおくて検証できないのでcreate table,insert intoの形式でサンプルを再提示して下さい
iorin

2018/05/15 07:19

サンプルデータのCREATE文とINSERT文を追記しました。
sazi

2018/05/15 07:35

出力結果のRD_PKはTBL1のRD_PKの値と違っているようですが
iorin

2018/05/15 07:46

失礼しました。修正しました。
yambejp

2018/05/15 07:49

insertするときの0xは文字列扱いなのでクォーテーションでくくってください
guest

回答2

0

ちょっと微妙ですがこんな感じでサブクエリ同士をjoinする

SQL

1select * from ( 2SELECT 3 RD.Item_No 4 , RD.RD_PK 5 , SUM(SD.Quantity) as SUM_SD_Quantity 6FROM 7 TBL1 RD 8 INNER JOIN TBL2 SD 9 ON RD.C_Cd = SD.C_Cd 10 AND RD.RH_PK = SD.RH_PK 11WHERE 12 RD.C_Cd = '01' 13 AND RD.O_No = 'A100' 14 AND RD.Item_No = SD.Item_No 15 16GROUP BY 17 SD.Item_No 18) as t1 19inner join ( 20SELECT RRD.Diff 21 , RRD.Item_No 22 ,SUM(RRD.Diff) as SUM_RRD_Diff 23FROM 24 TBL4 RRD 25 INNER JOIN TBL3 RRH 26 ON RRH.C_Cd = RRD.C_Cd 27 AND RRH.R_PK = RRD.R_PK 28WHERE 29 RRH.C_Cd = '01' 30 AND RRH.O_No = 'A100' 31GROUP BY 32 RRD.Diff 33 , RRD.Item_No 34) as t2 35on t1.Item_No=t2.Item_No 36

投稿2018/05/15 09:44

yambejp

総合スコア114572

バッドをするには、ログインかつ

こちらの条件を満たす必要があります。

iorin

2018/05/16 02:40

回答ありがとうございました。
guest

0

ベストアンサー

ぱっと見て、単に結合したものを集計すれば良さそうに見えます。

SQL

1SELECT RD.RH_PK, RD.RD_PK, RD.Item_no, sum(SD.Quantity), SUM(RRD.Diff) 2FROM TBL1 RD 3 INNER JOIN TBL2 SD 4 ON RD.RH_PK = SD.RH_PK 5 AND RD.C_Cd = SD.C_Cd 6 AND RD.Item_No = SD.Item_No 7 INNER JOIN TBL3 RRH 8 ON SD.RH_PK = RRH.RH_PK 9 AND SD.C_Cd = RRH.C_Cd 10 INNER JOIN TBL4 RRD 11 ON RRH.R_PK = RRD.R_PK 12 AND RRH.C_Cd = RRD.C_Cd 13WHERE RD.C_Cd = '01' 14 AND RD.O_No = 'A100' 15GROUP BY RD.RH_PK, RD.RD_PK, RD.Item_no

※QuantityとDiffはレコード増える仕様
この例ではDiffは1つですが複数行ある場合を仮定して頂きたいです。

sum(diff1), sum(diff2)のように増えるとしても、集計の単位が同じなら、sum()の項目を増やせば良いだけ

追記

質問のSQLでは動作しないので、推測での記述ですけど、以下でquantityとdiffは取り出せていると思います。
こういった場合、そのぞれの集計結果をどの項目で結びつけるかを明らかにすると迷わなくてすみます。
※diffの複数行については良く分からないので、質問にデータイメージを追記して下さい。

SQL

1select rd_sum.rh_pk, rd_sum.rd_pk, rd_sum.item_no, rd_sum.quantity, rrd_sum.diff 2from ( 3 select rd.rh_pk, rd.rd_pk, rd.item_no, sum(sd.quantity) as quantity 4 from tbl1 rd 5 inner join tbl2 sd 6 on rd.rh_pk = sd.rh_pk 7 and rd.rd_pk = sd.rd_pk 8 and rd.c_cd = sd.c_cd 9 where rd.c_cd = '01' 10 and rd.o_no = 'A100' 11 group by rd.rh_pk, rd.rd_pk, rd.item_no 12 ) rd_sum 13 inner join ( 14 select rrh.rh_pk, rrd.item_no, sum(rrd.diff) as diff 15 from tbl3 rrh 16 inner join tbl4 rrd 17 on rrh.r_pk = rrd.r_pk 18 and rrh.c_cd = rrd.c_cd 19 where rrd.c_cd = '01' 20 and rrh.o_no = 'A100' 21 group by rrh.rh_pk, rrd.item_no 22 ) rrd_sum 23 on rd_sum.rh_pk = rrd_sum.rh_pk 24 and rd_sum.item_no = rrd_sum.item_no 25order by item_no

追記2

データ件数が少ないならさほどレスポンスは落ちないでしょうから、条件部分を収斂したパターンも記述しておきます。

SQL

1select rd_sum.rh_pk, rd_sum.rd_pk, rd_sum.item_no, rd_sum.quantity, rrd_sum.diff 2from ( 3 select rd.rh_pk, rd.rd_pk, rd.c_cd, rd.o_no, rd.item_no, sum(sd.quantity) as quantity 4 from tbl1 rd 5 inner join tbl2 sd 6 on rd.rh_pk = sd.rh_pk 7 and rd.rd_pk = sd.rd_pk 8 and rd.c_cd = sd.c_cd 9 group by rd.rh_pk, rd.rd_pk, rd.c_cd, rd.o_no, rd.item_no 10 ) rd_sum inner join ( 11 select rrh.rh_pk, rrd.c_cd, rrh.o_no, rrd.item_no, sum(rrd.diff) as diff 12 from tbl3 rrh 13 inner join tbl4 rrd 14 on rrh.r_pk = rrd.r_pk 15 and rrh.c_cd = rrd.c_cd 16 group by rrh.rh_pk, rrd.c_cd, rrh.o_no, rrd.item_no 17 ) rrd_sum 18 on rd_sum.rh_pk = rrd_sum.rh_pk 19 and rd_sum.c_cd = rrd_sum.c_cd 20 and rd_sum.o_no = rrd_sum.o_no 21 and rd_sum.item_no = rrd_sum.item_no 22where rd_sum.c_cd='01' and rd_sum.o_no='A100' 23order by item_no

投稿2018/05/15 05:43

編集2018/05/16 02:54
sazi

総合スコア25138

バッドをするには、ログインかつ

こちらの条件を満たす必要があります。

iorin

2018/05/15 06:04

回答ありがとうございます。 提示頂きましたSQLだと以下のようになってしまいます。 |RH_PK|RD_PK|Item_No|SUM(SD.Quantity)|SUM(RRD.Diff)| |:--|:--:|--:|:--:|:--:| |10|36|1|4|12| |10|37|2|8|12| |10|38|3|8|12| |10|39|4|16|12|
iorin

2018/05/15 06:07

言葉足らずで申し訳ありません。 Diffが複数行というのはQuantityのようにPKが異なり、Item_Noが同一のものということです。
iorin

2018/05/15 07:32

TBL4のイメージと結果を質問に追記しました。
Orlofsky

2018/05/15 08:07

CREATE TABLE, CREATE INDEXや現状のデータをINSERTで提示した方が適切なコメントが付き易いかと。
iorin

2018/05/16 02:39

提示頂いたSQLで上手く取得できました。ありがとうございました。
guest

あなたの回答

tips

太字

斜体

打ち消し線

見出し

引用テキストの挿入

コードの挿入

リンクの挿入

リストの挿入

番号リストの挿入

表の挿入

水平線の挿入

プレビュー

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

ただいまの回答率
85.50%

質問をまとめることで
思考を整理して素早く解決

テンプレート機能で
簡単に質問をまとめる

質問する

関連した質問