確かにこのままでも実行は可能ですが、冗長にならないようにどうすればいいのかと思ったので質問させて頂きました。  
試した事は「my_goals」−「enemy_goals」ですが「Unknown columns」になってしまいました。

元となるSQLは、下記リンク先の正解部分です。from句に全てまとめるのでしょうか？  
バージョンは8.0.19です

```

    SELECT p1.kickoff, c1.name AS my_country, c2.name AS enemy_country, 
    c1.ranking AS my_ranking, c2.ranking AS enemy_ranking,
    (SELECT COUNT(g1.id) FROM goals g1 WHERE p1.id = g1.pairing_id) AS my_goals,
    (
    SELECT COUNT(g2.id) 
    FROM goals g2 
    LEFT JOIN pairings p2 ON p2.id = g2.pairing_id
    WHERE p2.my_country_id = p1.enemy_country_id AND p2.enemy_country_id = p1.my_country_id
    ) AS enemy_goals,
    -- 追加ここから
    (SELECT COUNT(g1.id) FROM goals g1 WHERE p1.id = g1.pairing_id) - ( 
    SELECT COUNT(g2.id) 
    FROM goals g2 
    LEFT JOIN pairings p2 ON p2.id = g2.pairing_id
    WHERE p2.my_country_id = p1.enemy_country_id AND p2.enemy_country_id = p1.my_country_id
    ) AS goal_diff
    -- 追加ここまで
    FROM pairings p1
    LEFT JOIN countries c1 ON c1.id = p1.my_country_id
    LEFT JOIN countries c2 ON c2.id = p1.enemy_country_id
    WHERE c1.group_name = 'C' AND c2.group_name = 'C'
    ORDER BY p1.kickoff, c1.ranking
    
```

[SQL練習問題 – 問17 | TECH
Projin](https://tech.pjin.jp/blog/2016/08/26/sql%E7%B7%B4%E7%BF%92%E5%95%8F%E9%A1%8C-%E5%95%8F17/)

> ### 問題：問題16の結果に得失点差を追加してください。
>
> 表示するカラム  
>  ・キックオフ日時  
>  ・自国名  
>  ・対戦相手国名  
>  ・自国FIFAランク  
>  ・対戦相手国FIFAランク  
>  ・自国のゴール数  
>  ・対戦国のゴール数  
>  ・得失点差（※追加！）
>
> ソート順  
>  ・キックオフ日時  
>  ・自国FIFAランク
>
>
> [![画像の説明をここに入力](https://i.stack.imgur.com/Y5s2U.jpg)](https://i.stack.imgur.com/Y5s2U.jpg)
CC BY-SA 4.0
2020-04-28T09:21:31.120
66070
2020-10-28T02:04:30.363
2020-04-29T05:28:25.793
3068
39860
question
mysql sql
クエリが冗長なので同じクエリを何回も書かないようにしたい
リンク先の模範回答で`my_goals`と`enemy_goals`カラムを求める副問い合わせを`goal_diff`カラムにコピペしている部分に関して回答します。

> 試した事は「my_goals」−「enemy_goals」ですが「Unknown columns」になってしまいました。

はい。  
select句で求めた`my_goals`カラムなどを、同一のselect句の中で使いまわすことはできません。(下のSQLはエラーになります)

```

    SELECT 1 AS a,
           2 - a AS b -- Unknown columns!
    FROM hoge; -- ダミーテーブル
    
```

冗長にならないためにはfrom句に全てまとめるのが正解のひとつです。  
変更前のSQLを大きな副問い合わせとして括弧で囲えば、その外側で`my_goals - enemy_goals`を取得することができます。  
冗長性は減りましたが、副問い合わせでSQLの階層が深くなりました。

```

    SELECT t.*, my_goals - enemy_goals AS goal_diff
    FROM (
    SELECT p1.kickoff, c1.name AS my_country, c2.name AS enemy_country, 
        c1.ranking AS my_ranking, c2.ranking AS enemy_ranking,
        (SELECT COUNT(g1.id) FROM goals g1 WHERE p1.id = g1.pairing_id) AS my_goals,
        (
            SELECT COUNT(g2.id) 
            FROM goals g2 
            LEFT JOIN pairings p2 ON p2.id = g2.pairing_id
            WHERE p2.my_country_id = p1.enemy_country_id AND p2.enemy_country_id = p1.my_country_id
        ) AS enemy_goals
    FROM pairings p1
    LEFT JOIN countries c1 ON c1.id = p1.my_country_id
    LEFT JOIN countries c2 ON c2.id = p1.enemy_country_id
    WHERE c1.group_name = 'C' AND c2.group_name = 'C'
    ORDER BY p1.kickoff, c1.ranking
    ) AS t;
    
```

もう一つの正解はwith句を使って試合・チームごとのゴール数を取得するクエリを分離する方法です。  
あまり短くなってはいませんが、副問い合わせをselect句の上に書くことができます。

```

    with goal_count as (
        select p.id id, count(g.id) value
        from pairings p, goals g
        where p.id = g.pairing_id
        group by p.id)
    SELECT p1.kickoff, c1.name AS my_country, c2.name AS enemy_country,
        c1.ranking AS my_ranking, c2.ranking AS enemy_ranking,
        ifnull(g1.value, 0) AS my_goals,
        ifnull(g2.value, 0) AS enemy_goals,
        ifnull(g1.value, 0) - ifnull(g2.value, 0) AS goal_diff
    FROM pairings p1
    LEFT JOIN countries c1 ON c1.id = p1.my_country_id
    LEFT JOIN goal_count g1 ON g1.id = p1.id,
         pairings p2
    LEFT JOIN countries c2 ON c2.id = p2.my_country_id
    LEFT JOIN goal_count g2 ON g2.id = p2.id
    WHERE p1.my_country_id = p2.enemy_country_id
    AND p1.enemy_country_id = p2.my_country_id
    AND c1.group_name = 'C' AND c2.group_name = 'C'
    ORDER BY p1.kickoff, c1.ranking;
    
```

余談ですが1つのSQLで全てを取得するとあまりに複雑になる場合は、SQLを分割して複数回クエリを発行することやプログラムで計算処理することも検討します。  
例えば`goal_diff`はSQLで取得せず、プログラムのループで求めるなどの方法です。

今回のSQLは大して複雑ではありませんし、練習問題なので上記の対応は要件を満たしていませんが、複雑なSQLは速度低下や不具合の原因になりやすいので冗長さに加えて複雑さを避けることも簡潔なSQL作成の視点として大事だと思います。
CC BY-SA 4.0
2020-04-29T02:55:05.540
66092
2020-04-29T02:55:05.540
9820
66070
answer