別テーブルの値で計算してCASEで評価するSQLを1文にまとめる

SQLの勉強をしている中で、中々手間取った上に記事にされてる方がいらっしゃらなかったので備忘録的にまとめておきます。

目的

タイトルの通り、1文で、結合・計算・比較をしてしまおう

例題

テーブル”売上表”とテーブル”商品情報”から
商品3つ"服(Cloth)""スカート(Skirt)""靴(Shoes)"が
１回の販売（伝票番号別）でそれぞれの目標販売額（ノルマ）を上回ったかを求める
なお、各テーブルと目標値は以下の通り
※（）内は型を意味しています。

欲しい結果

さて、概要の確認はOKでしょうか、ここからSQL組みます。

SQLその1：まずはテーブルの結合と計算

SELECT 売上表.伝票番号 , 売上表.数量 * 商品情報.価格 As "売上"
　FROM 売上表 LEFT JOIN 商品情報 ON 売上表.商品 = 商品情報.商品 ;

ここまでは普通の外部結合なので、いろいろなところで記事を見るかと思います。
これを実行すると

伝票番号 ❘　売上  ❘
--------+--------+---
      1 ❘　40000 ❘　
      2 ❘　 4000 ❘　
      3 ❘　11000 ❘　
      4 ❘　20000 ❘

こんな感じになっているはずです。

SQLその2：CASE式

CASEは式でありSELECT,FROM,WHERE,JOINなどの句ではない、という記述をよく見ますよね。
まぁそれは置いておいて、先ほどのノルマを式にします。

SELECT CASE
WHEN 売上表.数量 * 商品情報.価格 >= 30000 THEN 'たくさん売れた！'
WHEN 売上表.数量 * 商品情報.価格 >= 10000 THEN 'そこそこ売れた！'
WHEN 売上表.数量 * 商品情報.価格 < 10000 THEN '全然ダメ'
END

これで評価はできます。
さて、このCASE式をどこに入れるかで少し時間を食いました。

SQLその3：式を組み込み

その1とその2をくっつけます。

SELECT 売上表.伝票番号 , 売上表.数量 * 商品情報.価格 As "売上"

(SELECT CASE
WHEN 売上表.数量 * 商品情報.価格 >= 30000 THEN 'たくさん売れた！'
WHEN 売上表.数量 * 商品情報.価格 >= 10000 THEN 'そこそこ売れた！'
WHEN 売上表.数量 * 商品情報.価格 < 10000 THEN '全然ダメ'
END)

　FROM 売上表 LEFT JOIN 商品情報 ON 売上表.商品 = 商品情報.商品 ;

ただ、これで実行すると以下のようになります

伝票番号 ❘　売上   ❘  case
--------+--------+---------------
      1 ❘　40000  ❘　たくさん売れた！
      2 ❘　 4000  ❘　全然ダメ
      3 ❘　11000  ❘　そこそこ売れた！
      4 ❘　20000  ❘　たくさん売れた！

caseはノルマに変えたい！
そんなときはCASE式のEND以降に　AS 名称　を付け加えます

SELECT 売上表.伝票番号 , 売上表.数量 * 商品情報.価格 As "売上"

(SELECT CASE
WHEN 売上表.数量 * 商品情報.価格 >= 30000 THEN 'たくさん売れた！'
WHEN 売上表.数量 * 商品情報.価格 >= 10000 THEN 'そこそこ売れた！'
WHEN 売上表.数量 * 商品情報.価格 < 10000 THEN '全然ダメ'
END AS ノルマ)

　FROM 売上表 LEFT JOIN 商品情報 ON 売上表.商品 = 商品情報.商品 ;

これで実行するとこのようになるかと思います。

伝票番号 ❘　売上   ❘  ノルマ
--------+--------+---------------
      1 ❘　40000  ❘　たくさん売れた！
      2 ❘　 4000  ❘　全然ダメ
      3 ❘　11000  ❘　そこそこ売れた！
      4 ❘　20000  ❘　たくさん売れた！

まとめ

ポイントは通常のカラムはAS句で名称を変える場合、" "でくくらないといけませんが
CASE式にAS句で名称を変える場合はくくる必要がありません。
また、テーブルに保存されていない値で比較する場合は、上記のようにCASE式を、カラム名宣言するSELECT FROMの間に入れることで使えます。

お疲れさまでしたー。