在发现结果为真的WHEN子句时,CASE表达式的真假值判断会终止,剩余的WHEN子句会被忽略:
CASE WHEN col_1 IN ('a', 'b') THEN '第一'
WHEN col_1 IN ('a') THEN '第二'
ELSE '其他' END
注意:
- 统一各分支返回的数据类型.
- 记得写end.
- 写else子句的习惯,否则执行结果默认处理为null.
案例1
将已有编号方式转换为新的方式并统计
下面给出的group by引用select中定义的别名,case写在group by违反SQL规则(GROUP BY 子句比 SELECT 语句先执行):
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 district;
案例2
用一条 SQL 语句进行不同条件的统计
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的条件分支(代替where)
案例3
用 CHECK 约束定义多个列的条件关系
题目:女性员工工资必须在20w以上(蕴含式（conditional）的逻辑表达式，记作 P → Q)
CONSTRAINT check_salary CHECK
( CASE WHEN sex = '2'
THEN CASE WHEN salary > 200000
THEN 1 ELSE 0 END
ELSE 1 END = 1 )
案例4
在 UPDATE 语句里进行条件分支
要求
- 对当前工资为 30 万以上的员工，降薪 10%。
- 对当前工资为 25 万以上且不满 28 万的员工，加薪 20%。
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;
案例5
表之间的数据匹配
用以上表生成下面表:
| 课程名称 | 6 月 | 7 月 | 8 月 |
|---|---|---|---|
| 会计入门 | ○ | × | × |
| 财务知识 | × | × | ○ |
| 簿记考试 | ○ | × | × |
| 税务师 | ○ | ○ | ○ |
注:此sql实现可以使用in或exists谓词
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;
案例6
在 CASE 表达式中使用聚合函数
- 获取只加入了一个社团的学生的社团 ID。
- 获取加入了多个社团的学生的主社团 ID。
注:此处使用MAX,确保每个学生只被计算一次,确保在每个分组（每个学生）中只选择一个社团.
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;
case在执行时会被判断为一个固定值,因此可以用在聚合函数中.
习题实践 求两列中最大值
select key,
case when x < y then y
else x end as greatest
from Greatests;
拓展为三列,需要在分支中嵌套(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;
如果为四列或者五列,先行转列之后使用max函数:
select key,MAX(col) as greatest
from (
select key,x as col from Greatests
union all
select key,y as col from Greatests
unoin all
select key,z as col from Greate
)TMP
group by key;
其中上述使用union all产生重复数据(建立临时表进行行转列),之后使用group by进行排序,最后max进行去重.
注:Oracle 或 MySQL 可以使用下面函数进行求解:
SELECT key, GREATEST(GREATEST(x,y), z) AS greatest
FROM Greatests;