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

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

ただいまの
回答率

90.87%

  • MySQL

    5337questions

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

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

解決済

回答 2

投稿 編集

  • 評価
  • クリップ 0
  • VIEW 117

iorin

score 18

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

以下のような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↑

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

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

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

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

    クリップを取り消します

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

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

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

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

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

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

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

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

    質問の評価を下げる

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

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

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

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

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

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

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

    詳細な説明はこちら

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

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

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

質問への追記・修正、ベストアンサー選択の依頼

  • sazi

    2018/05/15 16:35

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

    キャンセル

  • iorin

    2018/05/15 16:46

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

    キャンセル

  • yambejp

    2018/05/15 16:49

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

    キャンセル

回答 2

checkベストアンサー

0

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

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

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

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

追記

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

select rd_sum.rh_pk, rd_sum.rd_pk, rd_sum.item_no, rd_sum.quantity, rrd_sum.diff
from (
        select  rd.rh_pk, rd.rd_pk, rd.item_no, sum(sd.quantity) as quantity
        from    tbl1 rd 
                inner join tbl2 sd 
                on    rd.rh_pk = sd.rh_pk 
                 and  rd.rd_pk = sd.rd_pk 
                 and  rd.c_cd = sd.c_cd 
        where   rd.c_cd = '01' 
           and  rd.o_no = 'A100' 
        group by  rd.rh_pk, rd.rd_pk, rd.item_no
     ) rd_sum
     inner join (
        select  rrh.rh_pk, rrd.item_no, sum(rrd.diff) as diff
        from    tbl3 rrh 
                inner join tbl4 rrd 
                on    rrh.r_pk = rrd.r_pk 
                 and  rrh.c_cd = rrd.c_cd 
        where   rrd.c_cd = '01' 
           and  rrh.o_no = 'A100' 
        group by  rrh.rh_pk, rrd.item_no
     ) rrd_sum
     on    rd_sum.rh_pk = rrd_sum.rh_pk 
      and  rd_sum.item_no = rrd_sum.item_no 
order by item_no

追記2

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

select rd_sum.rh_pk, rd_sum.rd_pk, rd_sum.item_no, rd_sum.quantity, rrd_sum.diff
from (
        select  rd.rh_pk, rd.rd_pk, rd.c_cd, rd.o_no, rd.item_no, sum(sd.quantity) as quantity
        from    tbl1 rd 
                inner join tbl2 sd 
                on    rd.rh_pk = sd.rh_pk 
                 and  rd.rd_pk = sd.rd_pk 
                 and  rd.c_cd = sd.c_cd 
        group by  rd.rh_pk, rd.rd_pk, rd.c_cd, rd.o_no, rd.item_no
     ) rd_sum inner join (
        select  rrh.rh_pk, rrd.c_cd, rrh.o_no, rrd.item_no, sum(rrd.diff) as diff
        from    tbl3 rrh 
                inner join tbl4 rrd 
                on    rrh.r_pk = rrd.r_pk 
                 and  rrh.c_cd = rrd.c_cd 
        group by  rrh.rh_pk, rrd.c_cd, rrh.o_no, rrd.item_no
     ) rrd_sum
     on    rd_sum.rh_pk = rrd_sum.rh_pk 
      and  rd_sum.c_cd = rrd_sum.c_cd
      and  rd_sum.o_no = rrd_sum.o_no 
      and  rd_sum.item_no = rrd_sum.item_no 
where rd_sum.c_cd='01' and rd_sum.o_no='A100' 
order by item_no

投稿

編集

  • 回答の評価を上げる

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

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

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

  • 回答の評価を下げる

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

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

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

  • 2018/05/15 15: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|

    キャンセル

  • 2018/05/15 15:07

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

    キャンセル

  • 2018/05/15 16:32

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

    キャンセル

  • 2018/05/15 17:07

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

    キャンセル

  • 2018/05/16 11:39

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

    キャンセル

0

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

select * from (
SELECT
  RD.Item_No
  , RD.RD_PK
  , SUM(SD.Quantity) as 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
) as t1
inner join (
SELECT   RRD.Diff
  , RRD.Item_No
  ,SUM(RRD.Diff) as 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 t2
on t1.Item_No=t2.Item_No

投稿

  • 回答の評価を上げる

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

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

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

  • 回答の評価を下げる

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

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

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

  • 2018/05/16 11:40

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

    キャンセル

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

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

関連した質問

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

  • MySQL

    5337questions

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