構文
構文は２つあり、それぞれの例は同じ挙動をする。
ただし検索CASE式は単純CASE式の機能を全て含む。
CASE式の評価はTRUEになるWHEN句が見つかった場合、後続のWHEN句は評価されない。
単純CASE式
CASE
sex
WHEN '1' THEN '男'
WHEN '2' THEN '女'
ELSE 'その他'
END
検索CASE式
CASE
WHEN sex = '1' THEN '男'
WHEN sex = '2' THEN '女'
ELSE 'その他'
END
仕様
CASE式は、ある列の値を別の値に読み替えることができる。
ただし返す値の型は一致している必要がある。
WHEN句の評価式は戻り値が真理値であるため、TRUE, FALSE, UNKNOWNのいずれかが戻る。
=, !=, >, LIKE, BETWEENといった述語が用いられる。
WHEN句がTRUEとなった場合、直後のTHEN句で指定された式が戻されて、CASE式は終了となる。
もし全てのWHEN句がTRUEとならなかった場合は、ELSEで指定された式が戻されて終了となる。
「式」であるため、CASE式は実行時に評価されて、１つの値に定まる。
例として、下記のようなテーブルを考える。
team
| sex | name |
|---|---|
| 2 | 花子 |
| 1 | 太郎 |
| 1 | 次郎 |
| 1 | 三郎 |
| 2 | 順子 |
| 1 | 道夫 |
| 2 | 由美 |
以下のようなクエリが発行された場合の結果を示す。
SELECT
CASE
WHEN sex = '1' THEN '男'
WHEN sex = '2' THEN '女'
ELSE 'その他'
END as sex_name,
name
FROM
team
;
以下のような結果を得る。
| sex_name | name |
|---|---|
| 女 | 花子 |
| 男 | 太郎 |
| 男 | 次郎 |
| 男 | 三郎 |
| 女 | 順子 |
| 男 | 道夫 |
| 女 | 由美 |
今回の例だと1や2というコードを、男や女という値に変換している。
また男や女の列名を「sex_name」として新規に命名している。
こうすることで、クエリ内の他の場所から参照することができる。
よくある使用例
既存のコード体系を分析用のコード体系に変換したい
県単位の人口が記録されているテーブルを考える。
pop
| pref_name | population |
|---|---|
| 東京 | 500 |
| 神奈川 | 300 |
| 福岡 | 250 |
| 埼玉 | 250 |
| 長崎 | 50 |
| 千葉 | 250 |
| 山形 | 100 |
これに対して地方単位の人口を知りたい場合、CASE式を使うと便利である。
SELECT
CASE
pref_name
WHEN '東京' THEN '関東'
WHEN '神奈川' THEN '関東'
WHEN '福岡' THEN '関東'
WHEN '埼玉' THEN '関東'
WHEN '長崎' THEN '関東'
WHEN '千葉' THEN '関東'
ELSE 'その他'
END as region,
SUM(population)
FROM
pop
GROUP BY
region
;
以下のような結果を得る。
| region | SUM(population) |
|---|---|
| 関東 | 1300 |
| 九州 | 300 |
| その他 | 100 |
行持ちテーブルを列持ちテーブルに変換
県別人口テーブルに性別列が追加された場合を考える。
pop_sex
| pref_name | sex | population |
|---|---|---|
| 東京 | 1 | 250 |
| 東京 | 2 | 250 |
| 神奈川 | 1 | 200 |
| 神奈川 | 2 | 100 |
| 福岡 | 1 | 150 |
| 福岡 | 2 | 100 |
| 埼玉 | 1 | 100 |
| 埼玉 | 2 | 150 |
| 長崎 | 1 | 20 |
| 長崎 | 2 | 30 |
| 千葉 | 1 | 120 |
| 千葉 | 2 | 130 |
| 山形 | 1 | 50 |
| 山形 | 2 | 50 |
これに対して県単位の人口を男女別に知りたい場合もCASE式が有効である。
SELECT
pref_name,
SUM(CASE
WHEN sex = '1' THEN population
ELSE 0
END
) AS count_male,
SUM(CASE
WHEN sex = '2' THEN population
ELSE 0
END
) AS count_female,
FROM
pop_sex
GROUP BY
pref_name
;
以下のような結果を得る。
| pref_name | count_male | count_female |
|---|---|---|
| 東京 | 250 | 250 |
| 神奈川 | 200 | 100 |
| 福岡 | 150 | 100 |
| 埼玉 | 100 | 150 |
| 長崎 | 20 | 30 |
| 千葉 | 120 | 130 |
| 山形 | 50 | 50 |
条件付きUPDATE
案件ごとの報酬額テーブルに対して、次のような条件で更新をかけたいとする。
- 250,000以上の案件は10%減額
- 100,000以上、200,000未満の案件は20%増額
job
| job_name | reward |
|---|---|
| デザイン | 190,000 |
| 施工 | 80,000 |
| 管理 | 280,000 |
| 広報 | 170,000 |
UPDATE
job
SET
reward =
CASE
WHEN reward >= 250000 THEN reward * 0.9
WHEN reward >= 100000 AND reward THEN reward * 1.2
ELSE reward
END
;
まとめ
- CASE式は、ある列の値を別の値に読み替えることができる
- 「式」であるため、CASE式は実行時に評価されて、１つの値に定まる
- 返す値の型は一致している必要がある
- CASE式は列名や定数を書ける場所にはどこでも書ける
- CASE式を用いることで複数のSQL文を１つにまとめることができる