CASE式

既存のコード体系を新しい体系に変換して集計

県コードを地方単位にまとめ、その単位で人口を集計する場合。

集計元の表:PopTbl
県名pref_name人口population
徳島100
高知200
愛媛150
香川200
福岡300
佐賀100
長崎200
東京400
群馬50


集計結果
地方名人口
四国650
九州600
その他450
SELECT CASE pref_name WHEN '徳島' THEN '四国' WHEN '香川' THEN '四国' WHEN '愛媛' THEN '四国' WHEN '高知' THEN '四国' WHEN '福岡' THEN '九州' WHEN '佐賀' THEN '九州' WHEN '長崎' THEN '九州' ELSE 'その他' END AS district ,SUM(population) FROM PopTbl GROUP BY CASE pref_name WHEN '徳島' THEN '四国' WHEN '香川' THEN '四国' WHEN '愛媛' THEN '四国' WHEN '高知' THEN '四国' WHEN '福岡' THEN '九州' WHEN '佐賀' THEN '九州' WHEN '長崎' THEN '九州' ELSE 'その他' END ;

異なる条件の集計

男女別・県別の人数の合計を求める。

集計元の表:PopTbl2
県名pref_name性別sex人口population
徳島160
徳島240
高知1100
高知2100
愛媛1100
愛媛250
香川1100
香川2100
福岡1100
福岡2200
佐賀120
佐賀280
長崎1125
長崎2125
東京1250
東京2150


集計結果
県名
徳島6040
高知100100
愛媛10050
香川100100
福岡100200
佐賀2080
長崎125125
東京250150
SELECT pref_name /* 男性の人口 */ ,SUM(CASE WHEN sex = '1' THEN population ELSE 0 END) AS cnt_m /* 女性の人口 */ ,SUM(CASE WHEN sex = '2' THEN population ELSE 0 END) AS cnt_f FROM PopTbl2 GROUP BY pref_name ;

全国と四国の合計も表頭に加えてクロス集計する。

SELECT sex ,SUM(population) AS 全国 ,SUM(CASE WHEN pref_name = '徳島' THEN population ELSE 0 END) AS 徳島 ,SUM(CASE WHEN pref_name = '香川' THEN population ELSE 0 END) AS 香川 ,SUM(CASE WHEN pref_name = '愛媛' THEN population ELSE 0 END) AS 愛媛 ,SUM(CASE WHEN pref_name = '高知' THEN population ELSE 0 END) AS 高知 ,SUM(CASE WHEN pref_name IN ('徳島', '香川', '愛媛', '高知') THEN population ELSE 0 END) AS 四国 FROM PopTbl2 GROUP BY sex ;

CASEを使って条件を分岐させたUPDATE

(1)現在の給料が30万以上の社員は10%の減給。
(2)現在の給料が25万以上28万未満の社員は20%の昇給。

Salaries
namesalary
山田300000
鈴木270000
田中220000
川本290000
UPDATE Salaries SET Salary = CASE WHEN salary >= 300000 THEN salary * 0.9 WHEN salary >= 250000 AND salary < 280000 THEN salary * 1.2 ELSE salary END; (*注)ELSE句を省略すると、給料がNULLになってしまいます。

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

クロス表の作成

講座マスタ:CourseMaster
講座IDcourse_id講座名course_name
1経理入門
2財務入門
3簿記入門
4検定対策
開講講座:OpenCourses
年月month講座IDcourse_id
2010061
2010063
2010064
2010074
2010082
2010084


開講状況
course_name6月7月8月
経理入門××
財務入門××
簿記入門××
検定対策
SELECT course_name ,CASE WHEN course_id IN (SELECT course_id FROM OpenCourses WHERE month = 200706 ) THEN '○' ELSE '×' END AS "6月" ,CASE WHEN course_id IN (SELECT course_id FROM OpenCourses WHERE month = 200707 ) THEN '○' ELSE '×' END AS "7月" ,CASE WHEN course_id IN (SELECT course_id FROM OpenCOurses WHERE month = 200708 ) THEN '○' ELSE '×' END AS "8月" FROM CourseMaster ;

CASE式の中で集約関数を使う

(1)1つだけのクラブに所属している学生についてはそのクラブIDを取得する。
(2)複数のクラブに所属している学生については主なクラブのIDを取得する。

StudentClub
学生番号std_idクラブIDclub_id主なクラブフラグmain_club_flg
1001Y
1002N
2002N
2003Y
2004N
3004N
4005N
5006N



std_idmain_club
1001
2003
3004
4005
5006
SELECT std_id ,CASE WHEN COUNT(*) = 1 THEN MAX(club_id) ELSE MAX(CASE WHEN main_club_flg = 'Y' THEN club_id ELSE NULL END) END AS main_club FROM StudentClub GROUP BY std_id ;

複数列の最大値

Greatests
keyxyz
A123
B552
C471
D335



keygreatest
A2
B5
C7
D3

xとyの最大値を求める

SELECT key ,CASE WHEN x < y THEN y ELSE x END AS greatest FROM Greatests ;

x、y、zの最大値を求める

(1)CASE式を使う

SELECT key ,CASE WHEN CASE WHEN x < y THEN y ELSE x END < z THEN z ELSE CASE WHEN x < y THEN y ELSE x END END AS greatest FROM Greatests ; (2)行列変換してMAX関数を使う SELECT key ,MAX(col) AS Greatest FROM (SELECT key ,x AS col FROM Greatests UNION ALL SELECT key ,y AS col FROM Greatests UNION ALL SELECT key ,z AS col FROM Greatests ) TMP GROUP BY key ;

ORDER BYでソート列

Greatests
keyxyz
A123
B552
C471
D335

key列を「B-A-D-C」の順で出力する。

(方法1) SELECT key FROM Greatests ORDER BY CASE key WHEN 'B' THEN 1 WHEN 'A' THEN 2 WHEN 'D' THEN 3 WHEN 'C' THEN 4 ELSE NULL END ; (方法2) SELECT key ,CASE key WHEN 'B' THEN 1 WHEN 'A' THEN 2 WHEN 'D' THEN 3 WHEN 'C' THEN 4 ELSE NULL END AS sort_key FROM Greatests ORDER BY sort_key ;