

SQL文について質問があります。  
以下のようなテーブルがあったとします。  
[![画像の説明をここに入力](https://i.stack.imgur.com/PZ8Ey.png)](https://i.stack.imgur.com/PZ8Ey.png)  
[![画像の説明をここに入力](https://i.stack.imgur.com/OR9Km.png)](https://i.stack.imgur.com/OR9Km.png)  
これに対して塗料だけの在庫データを出して、カラーコード毎の総重量を付け加えたいです。  
イメージは以下のような結果です。  
[![画像の説明をここに入力](https://i.stack.imgur.com/uxcPp.png)](https://i.stack.imgur.com/uxcPp.png)

そのために以下のようなクエリを書きました。

[code]

    SELECT
        X.*,
        Y.総重量
    FROM
        (
            SELECT
                カラーコード,
                重量
            FROM
                在庫
                INNER JOIN
                    アイテムマスタ
                ON  在庫.アイテムコード = アイテムマスタ.アイテムコード
            WHERE
                アイテムマスタ.分類 = '塗料'
        ) X
        LEFT JOIN
            (
                SELECT
                    カラーコード,
                    SUM(重量) as 総重量
                FROM
                    在庫
                    INNER JOIN
                        アイテムマスタ
                    ON  在庫.アイテムコード = アイテムマスタ.アイテムコード
                WHERE
                    アイテムマスタ.分類 = '塗料'
                GROUP BY
                    在庫.カラーコード
            ) Y
        ON  X.カラーコード = Y.カラーコード
    
[/code]

カラーコード毎の総重量を求めるために同じようなサブクエリを同士LEFT JOINしています。  
この同じようなクエリをすっきりさせて求める結果を出す方法はないでしょうか。

[code]

    SELECT
        X.*,
        SELECT SUM(重量) ～～
    
[/code]

みたいな感じで同じようなサブクエリをJOINせずにできないかと思って色々試しましたがうまくいきませんでした。  
このやり方でしか出せないでしょうか。  
またWITH区は使わない方法でお願いします。  
このSQLは実際には実行していないのでエラーが出るかもしれないことをご了承ください。


CC BY-SA 4.0
2020-05-16T13:50:11.263
66676
2020-05-16T23:40:58.357
40183
question
sql
SQLで同じようなクエリがある場合の冗長性の排除(WITHを使わない方法)


塗料の在庫のビューを作成すれば少しは冗長性が排除できます。

【ビュー作成のSQL】

[code]

    create view 塗料在庫
     as select A.カラーコード, A.重量
        from 在庫 A, アイテムマスタ B
        where A.アイテムコード = B.アイテムコード AND B.分類 = '塗料';
    
[/code]

【検索のSQL】

[code]

    select A.カラーコード, A.重量, (select sum(X.重量)
                                   from 塗料在庫 X
                                   where A.カラーコード = X.カラーコード
                                   group by X.カラーコード) AS カラーコード毎の総重量
    from 塗料在庫 A;
    
[/code]

* * *

カラーコード毎の総重量のビューを作成すれば、さらに検索のSQLが簡単になります。

【ビュー作成のSQL】

[code]

    create view カラーコード毎の総重量
     as select カラーコード, sum(重量) as カラーコード毎の総重量
        from 在庫
        group by カラーコード;
    
[/code]

【検索のSQL】

[code]

    select A.カラーコード, A.重量, X.カラーコード毎の総重量
    from 塗料在庫 A, カラーコード毎の総重量 X
    where A.カラーコード = X.カラーコード;
    
[/code]


CC BY-SA 4.0
2020-05-16T16:53:55.080
66680
2020-05-16T16:53:55.080
35558
66676
answer


MySQL 8.0.20 での場合ですが、「カラーコード」と「重量」でグルーピングを行って、総重量は Window 関数で求めます。

[code]

    SELECT
      `カラーコード`,
      `重量`,
      ROUND(
        SUM(SUM(`重量`))
        OVER (PARTITION BY `カラーコード`), 1) AS `カラーコード毎の総重量`
    FROM 
      `在庫`
    WHERE
      `アイテムコード` IN
        (SELECT `アイテムコード` FROM `アイテムマスタ` WHERE `分類` = '塗料')
    GROUP BY `カラーコード`, `重量`;
    
    
    +--------------------+--------+-----------------------------------+
    | カラーコード         | 重量    | カラーコード毎の総重量               |
    +--------------------+--------+-----------------------------------+
    |                100 |    1.2 |                               4.7 |
    |                100 |    3.5 |                               4.7 |
    |                300 |    1.8 |                               1.8 |
    +--------------------+--------+-----------------------------------+
    
[/code]


CC BY-SA 4.0
2020-05-16T19:04:41.573
66682
2020-05-16T19:04:41.573
66676
answer


相関サブクエリで出来ます

[code]

    SELECT
        X.カラーコード,
        X.重量,
        (SELECT SUM(重量) FROM 在庫 Y WHERE Y.カラーコード=X.カラーコード AND Y.アイテムコード=X.アイテムコード) AS 総重量
    FROM
        在庫 X
    WHERE 
        X.アイテムコード IN (SELECT アイテムコード FROM アイテムマスタ WHERE 分類='塗料')
    
[/code]


CC BY-SA 4.0
2020-05-16T23:40:58.357
66694
2020-05-16T23:40:58.357
9811
66676
answer