多个 WHEN 子句的条件之间,不要有交集
在 SQL 语句中,当 WHEN 子句为真时,CASE 表达式的真假值判断就会中止,而剩余的 WHEN 子句会被忽略;因此,为了避免引起混乱,使用 WHEN 子句时要注意条件的排他性;
翻译:当一个 WHEN 子句的条件包含了其它 WHEN 子句的条件,那么被包含的 WHEN 子句不会被执行;
--
例:这样写的话,结果里不会出现 “第二”
CASE WHEN col_1 IN ('a', 'b') THEN '第一'
WHEN col_1 IN ('a') THEN '第二'
ELSE '其他' END
示例代码 1:把县编号转换为地区编号
注意
:GROUP BY 后要跟 CASE 的表达式,尽量不要跟表达式的别名 district,因为 GROUP BY 子句比 SELECT 子句先执行,如果使用了别名则违反了标准的 SQL 规则;(但是在 MySQL 和 PostgreSQL 中可以使用别名,而在 SQL Server、Oracle、DB2 中不可以使用别名;)
-- 把县编号转换为地区编号
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;
-- 按人口数量等级划分都道府县
SELECT CASE WHEN population < 100 THEN '01'
WHEN population >= 100 AND population < 200 THEN '02'
WHEN population >= 200 AND population < 300 THEN '03'
WHEN population >= 300 THEN '04'
ELSE NULL END AS pop_class,
COUNT(*) AS cnt
GROUP BY CASE WHEN population < 100 THEN '01'
WHEN population >= 100 AND population < 200 THEN '02'
WHEN population >= 200 AND population < 300 THEN '03'
WHEN population >= 300 THEN '04'
ELSE NULL END;
-- 返回结果集
pop_class cnt
----------------
01 1
02 2
03 3
04 2
--
男性人口
SUM
(
CASE
WHEN
sex
=
'
1
'
THEN
population)
AS
cnt_m,
SUM
(
CASE
WHEN
sex
=
'
2
'
THEN
population)
AS
cnt_f
FROM
PopTbl2
GROUP
BY
pref_name;
例:限定所有女性员工的工资不能高于 20 万
注:
此处嵌套了 CASE 表达式,用于同时满足多种条件的时候
;(如,此例中必须同时满足:sex = '2'、salary <= 200000)
另外:添加 CHECK 约束时,如果不是针对多有情况的值设定校验条件,不能使用 AND;比如此例中,如果使用(sex = '2' AND salary <= 200000),则表示只允许插入 sex = ‘2’ 并且不高于 200000 的薪水;
-- CASE 表达式是可以嵌套的,进行多条件刷选
CONSTRINT check_salary CHECK
(CASE WHEN sex = '2'
THEN CASE WHEN salary <= 200000
THEN 1 ELSE 0 END
ELSE 1 END 1)
SET
salary
=
CASE
WHEN
salary
>
300000
THEN
salary
*
0.9
WHEN
salary
>
250000
AND
salary
<=
280000
THEN
salary
*
1.2
ELSE
salary
END
;
注:
ELSE 后要返回原数据,即不再条件范围内的员工的薪水不变
;(如果没有 ELSE ,则会将 salary 中不满足 WHEN 条件的值更新为 NULL)
UPDATE SomeTable
SET p_key = CASE WHEN p_key = 'a' THEN 'b'
WHEN p_key = 'b' THEN 'a'
ELSE p_key END
WHERE p_key IN ('a', 'b');
-- 表的匹配:使用 IN 谓词
SELECT course_name,
CASE WHEN course_id IN ( SELECT course_id FROM OpenCourse
WHERE month = 200706) THEN '○'
ELSE '×' END AS '6月',
CASE WHEN course_id IN ( SELECT course_id FROM OpenCourse
WHERE month = 200707) THEN '○'
ELSE '×' END AS '7月',
CASE WHEN course_id IN ( SELECT course_id FROM OpenCourse
WHERE month = 200708) THEN '○'
ELSE '×' END AS '8月'
FROM CourseMaster;
-- 表的匹配:使用 EXISTS 谓词
SELECT CM.course_name,
CASE WHEN EXISTS ( SELECT OC.course_id FROM OpenCourse AS OC
WHERE month = 200706
AND OC.course_id = CM.course_id) THEN '○'
ELSE '×' END AS "6月",
CASE WHEN EXISTS ( SELECT OC.course_id FROM OpenCourse AS OC
WHERE month = 200707
AND OC.course_id = CM.course_id) THEN '○'
ELSE '×' END AS "7月",
CASE WHEN EXISTS ( SELECT OC.course_id FROM OpenCourse AS OC
WHERE month = 200708
AND OC.course_id = CM.course_id) THEN '○'
ELSE '×' END AS "8月",
FROM CourseMaster AS CM;
6、场景六:在 CASE 表达式中使用聚合函数
例:表 StudentClud,主键是 “学号、社团 ID”,其中有的学生加入了多个社团,有的学生只加入了一个社团,对于加入多个社团的学生,将其 “主社团标志” 设置为 Y 或者 N 来表明哪个社团是他的主社团;对于只加入一个社团的学生,将其 “主社团标志” 设置为 N;
需求:获取所有学生的学号,及其加入的主社团的社团 ID;
CASE
WHEN
COUNT
(
*
)
=
1
--
只加入了一个社团的学生
THEN
club_id
ELSE
CASE
WHEN
main_club_flg
=
'
Y
'
THEN
club_id
ELSE
NULL
END
END
AS
main_club
FROM
StudentClub
GROUP
BY
std_id;