便利なCASE文

条件分岐の際にとっても便利なので簡単なサンプルを利用してメモ

CASE文の書式

※ 各分岐が返すデータ型を統一し、ELSEを必ず入れる

-- 単純 CASE式
CASE sex
  WHEN '1' THEN '男'
  WHEN '2' THEN '女'
ELSE 'その他' END

-- 検索 CASE式
CASE WHEN sex = '1' THEN '男'
     WHEN sex = '2' THEN '女'
ELSE 'その他' END

例１）下記の体系を新しい体系へ変換

以下のテーブルがあったとして(ノーマルテーブル)

県名	人口
青森	200
秋田	600
山形	500
岩手	300
東京	200
茨城	100
徳島	100
愛媛	400
香川	500
高知	200

集計結果を以下のようにしたい

地域名	人口
東北	1600
四国	1200
その他	300

SELECT CASE 県名
  WHEN '青森' THEN '東北'
  WHEN '岩手' THEN '東北'
  WHEN '秋田' THEN '東北'
  WHEN '山形' THEN '東北'
  WHEN '徳島' THEN '四国'
  WHEN '愛媛' THEN '四国'
  WHEN '香川' THEN '四国'
  WHEN '高知' THEN '四国'
ELSE 'その他' END AS distinct,
SUM(人口)
FROM ノーマルテーブル
GROUP BY CASE 県名
  WHEN '青森' THEN '東北'
  WHEN '岩手' THEN '東北'
  WHEN '秋田' THEN '東北'
  WHEN '山形' THEN '東北'
  WHEN '徳島' THEN '四国'
  WHEN '愛媛' THEN '四国'
  WHEN '香川' THEN '四国'
  WHEN '高知' THEN '四国'
ELSE 'その他' END;

※ SELECT分をコピーするという事が大事！

例２）UPDATE - id（主キー）1と2を入れ替える -

サンプルテーブル

id	value1	value2
1	a	あ
2	b	い
3	c	い

下記の様にするのもいいが３回実行するのは無駄

-- 1. id(1)の値を4へ
UPDATE サンプルテーブル
  SET id = '4'
  WHERE id = '1';
-- 1. id(2)の値を1へ
UPDATE サンプルテーブル
  SET id = '1'
  WHERE id = '2';
-- 1. id(4)の値を2へ
UPDATE サンプルテーブル
  SET id = '2'
  WHERE id = '4';

CASE式で

UPDATE サンプルテーブル
  SET id = CASE 
           WHEN id = '1' THEN '2'
           WHEN id = '2' THEN '1'
           ELSE id END
  WHERE id IN ('1', '2');

例３）テーブル同士のマッチング

TEAM_MST

ID	TEAM_NAME
1	赤坂ライオンズ
2	大門ジャイアンツ
3	田町イーグルス
4	六本木ロッカーズ

SCHEDULES

MONTH	TEAM_ID
201507	1
201507	3
201507	4
201508	4
201509	2
201509	4

TEAM_NAME        ７月  8月  9月
---------        ---- ---- ----
赤坂ライオンズ     勝　　負　　負
大門ジャイアンツ   負　　負　　勝
田町イーグルス     勝　　負　　負
六本木ロッカーズ   勝　　勝　　勝

上記のように勝ち負けを表にしたい時

SELECT A.TEAM_NAME,
       CASE
       WHEN EXISTS
         (SELECT TEAM_ID FROM SCHEDULES B
            WHERE MONTH = 201507
              AND B.TEAM_ID = A.TEAM_ID) THEN '勝'
       ELSE '負' END AS '7月',
       WHEN EXISTS
         (SELECT TEAM_ID FROM SCHEDULES B
            WHERE MONTH = 201508
              AND B.TEAM_ID = A.TEAM_ID) THEN '勝'
       ELSE '負' END AS '8月',
       WHEN EXISTS
         (SELECT TEAM_ID FROM SCHEDULES B
            WHERE MONTH = 201509
               AND B.TEAM_ID = A.TEAM_ID) THEN '勝'
       ELSE '負' END AS '9月'
     FROM TEAM_MST A;

INでも実装可能だがINとEXISTSで迷ったらEXSITSのパフォーマンスは良いらしい。
サブクエリで主キーのインデックスを利用できる為