集計関数を複数のテーブルで行いたいです。
以下のような4つのテーブルが存在します。
TBL1
RH_PK | RD_PK | C_Cd | O_No | Item_No |
---|---|---|---|---|
10 | 41 | 01 | A100 | 1 |
10 | 42 | 01 | A100 | 2 |
10 | 43 | 01 | A100 | 3 |
10 | 44 | 01 | A100 | 4 |
TBL2
RH_PK | RD_PK | S_PK | C_Cd | Item_No | Quantity |
---|---|---|---|---|---|
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 |
TBL3(TBL1とTBL4の紐付けに必要)
RH_PK | R_PK | C_Cd | O_No |
---|---|---|---|
10 | 1 | 01 | A100 |
TBL4
R_PK | D_PK | C_Cd | Item_No | Diff |
---|---|---|---|---|
1 | 31 | 01 | 1 | 1 |
1 | 32 | 01 | 2 | 1 |
1 | 33 | 01 | 3 | 2 |
1 | 34 | 01 | 4 | 2 |
今回欲しい結果
※QuantityとDiffはレコード増える仕様
この例ではDiffは1つですが複数行ある場合を仮定して頂きたいです。
RH_PK | RD_PK | Item_No | SUM(SD.Quantity) | SUM(RRD.Diff) |
---|---|---|---|---|
10 | 41 | 1 | 1 | 1 |
10 | 42 | 2 | 2 | 1 |
10 | 43 | 3 | 2 | 2 |
10 | 44 | 4 | 4 | 2 |
それぞれ、別の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_PK | D_PK | C_Cd | Item_No | Diff |
---|---|---|---|---|
1 | 31 | 01 | 1 | 1 |
1 | 32 | 01 | 2 | 1 |
1 | 33 | 01 | 3 | 2 |
1 | 34 | 01 | 4 | 2 |
1 | 35 | 01 | 1 | 1 |
1 | 36 | 01 | 2 | 2 |
1 | 37 | 01 | 3 | 0 |
1 | 38 | 01 | 4 | 1 |
追記後の取得したい結果
RH_PK | RD_PK | Item_No | SUM(SD.Quantity) | SUM(RRD.Diff) |
---|---|---|---|---|
10 | 41 | 1 | 1 | 2 |
10 | 42 | 2 | 2 | 3 |
10 | 43 | 3 | 2 | 2 |
10 | 44 | 4 | 4 | 3 |
↑追記2↑
何か良い方法があれば教えて頂きたいです。
よろしくお願いします。
回答2件
あなたの回答
tips
プレビュー