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 徳島 1 60 徳島 2 40 高知 1 100 高知 2 100 愛媛 1 100 愛媛 2 50 香川 1 100 香川 2 100 福岡 1 100 福岡 2 200 佐賀 1 20 佐賀 2 80 長崎 1 125 長崎 2 125 東京 1 250 東京 2 150

⇒

集計結果 県名 男 女 徳島 60 40 高知 100 100 愛媛 100 50 香川 100 100 福岡 100 200 佐賀 20 80 長崎 125 125 東京 250 150

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

name	salary
山田	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 201006 1 201006 3 201006 4 201007 4 201008 2 201008 4

⇒

開講状況 course_name 6月 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 100 1 Y 100 2 N 200 2 N 200 3 Y 200 4 N 300 4 N 400 5 N 500 6 N

⇒

std_id main_club 100 1 200 3 300 4 400 5 500 6

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 key x y z A 1 2 3 B 5 5 2 C 4 7 1 D 3 3 5

⇒

key greatest A 2 B 5 C 7 D 3

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

key	x	y	z
A	1	2	3
B	5	5	2
C	4	7	1
D	3	3	5

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

(方法１) 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 ; (方法２) 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 ;