SQL中的 CASE WHEN用法详解

news/发布时间2024/5/15 15:22:40

SQL中的 CASE WHEN用法详解

在 SQL 中，CASE WHEN 语句通常用于根据条件执行不同的操作。

1. 基本用法

SELECTCASEWHEN condition1 THEN result1WHEN condition2 THEN result2...ELSE default_resultEND AS alias_name
FROMtable_name;

CASE 开始一个 CASE WHEN 语句块，END 结束该块。
按顺序检查每个条件，并在找到第一个为真的条件时返回相应的结果。
如果没有条件为真，则返回 ELSE 部分的默认结果（可选）。
允许在 WHEN 子句中使用表达式作为条件，而不仅仅是列或单个值。

2. 举例

根据学生的成绩, 分为 A B C 和 D 四个等级, A: 大于等于90; B: [80,90); C: [60,80); D:小于60;

-- 数据准备
WITH t_score_info AS ( -- 员工信息表
SELECT * FROM (VALUES    (1001, 'lyf', 83),(1002, 'zyb', 72),(1003, 'whl', 45),(1004, 'lxm', 64),(1005, 'szy', 92),(1006, 'xjp', 31),(1007, 'ply', 42),(1008, 'wyb', 88),(1009, 'spx', 72),(1010, 'yjj', 88),(1011, 'teg', 98),(1012, 'hxj', 61)
) AS table_name(user_id, name, score)
)
-- CASE WHEN 使用
SELECT user_id, name, score, CASE WHEN score >= 90 THEN 'A'WHEN score >= 80 AND score < 90 THEN 'B'WHEN score >= 60 AND score < 80 THEN 'C'ELSE 'D'END AS grades
FROM t_score_info
;

user_id	name	score	grades
1011	teg	98	A
1005	szy	92	A
1008	wyb	88	B
1010	yjj	88	B
1001	lyf	83	B
1002	zyb	72	C
1009	spx	72	C
1004	lxm	64	C
1012	hxj	61	C
1003	whl	45	D
1007	ply	42	D
1006	xjp	31	D

3. 使用 CASE WHEN 语句进行数据分组

-- 数据准备
WITH t_score_info AS ( -- 员工信息表
SELECT * FROM (VALUES    (1001, 'lyf', 83, 26),(1002, 'zyb', 72, 37),(1003, 'whl', 45, 25),(1004, 'lxm', 64, 46),(1005, 'szy', 92, 36),(1006, 'xjp', 31, 65),(1007, 'ply', 42, 67),(1008, 'wyb', 88, 25),(1009, 'spx', 72, 46),(1010, 'yjj', 88, 16),(1011, 'teg', 98, 18),(1012, 'hxj', 61, 48)
) AS table_name(user_id, name, score, age)
)-- 使用 CASE WHEN 语句进行数据分组
SELECT  CASE WHEN age >= 60 THEN '老年'WHEN age >= 40 AND age < 60 THEN '中年'WHEN age >= 20 AND age < 40 THEN '青年'ELSE '少年'END AS age_group, COUNT(1) AS ct
FROM t_score_info
GROUP BY age_group
;

age_group	ct
少年	2
老年	2
中年	3
青年	5

4. 使用 CASE WHEN 语句修改字段值

-- 数据准备
WITH t_score_info AS ( -- 员工信息表
SELECT * FROM (VALUES    (1001, 'lyf', 83, 26, 'C'),(1002, 'zyb', 72, 37, 'B'),(1003, 'whl', 45, 25, 'C'),(1004, 'lxm', 64, 46, 'C'),(1005, 'szy', 92, 36, 'B'),(1006, 'xjp', 31, 65, 'C'),(1007, 'ply', 42, 67, 'B'),(1008, 'wyb', 88, 25, 'C'),(1009, 'spx', 72, 46, 'B'),(1010, 'yjj', 88, 16, 'B'),(1011, 'teg', 98, 18, 'A'),(1012, 'hxj', 61, 48, 'B')
) AS table_name(user_id, name, score, age, grades)
)-- 使用 CASE WHEN 语句修改字段值: 将分数分分数段展示, 不展示具体分数值
SELECT user_id, name, CASE WHEN score >= 90 THEN '90+'WHEN score >= 80 AND score < 90 THEN '80-90'WHEN score >= 60 AND score < 80 THEN '60-80'ELSE '0-60'END AS score
FROM t_score_info
;

user_id	name	score
1001	lyf	80-90
1002	zyb	60-80
1003	whl	0-60
1004	lxm	60-80
1005	szy	90+
1006	xjp	0-60
1007	ply	0-60
1008	wyb	80-90
1009	spx	60-80
1010	yjj	80-90
1011	teg	90+
1012	hxj	60-80

5. CASE WHEN 和聚合函数一起使用

-- 数据准备
WITH t_score_info AS ( -- 员工信息表
SELECT * FROM (VALUES    (1001, 'lyf', 83, 26, 'C'),(1002, 'zyb', 72, 37, 'B'),(1003, 'whl', 45, 25, 'C'),(1004, 'lxm', 64, 46, 'C'),(1005, 'szy', 92, 36, 'B'),(1006, 'xjp', 31, 65, 'C'),(1007, 'ply', 42, 67, 'B'),(1008, 'wyb', 88, 25, 'C'),(1009, 'spx', 72, 46, 'B'),(1010, 'yjj', 88, 16, 'B'),(1011, 'teg', 98, 18, 'A'),(1012, 'hxj', 61, 48, 'B')
) AS table_name(user_id, name, score, age, grades)
)-- CASE WHEN 和 聚合函数 一起使用: 计算各个年龄段的总分数
SELECT  SUM(CASE WHEN age >= 60 THEN score ELSE 0 END) AS `老年组总分` , SUM(CASE WHEN age >= 40 AND age < 60 THEN score ELSE 0 END) AS `中年组总分` , SUM(CASE WHEN age >= 20 AND age < 40 THEN score ELSE 0 END) AS `青年组总分` , SUM(CASE WHEN age < 20 THEN score ELSE 0 END) AS `少年组总分` 
FROM t_score_info
;

老年组总分	中年组总分	青年组总分	少年组总分
73	197	380	186

6. CASE WHEN 的嵌套使用

-- 数据准备
WITH t_score_info AS ( -- 员工信息表
SELECT * FROM (VALUES    (1001, 'lyf', 83, 26, 'C'),(1002, 'zyb', 72, 37, 'B'),(1003, 'whl', 45, 25, 'C'),(1004, 'lxm', 64, 46, 'C'),(1005, 'szy', 92, 36, 'B'),(1006, 'xjp', 31, 65, 'C'),(1007, 'ply', 42, 67, 'B'),(1008, 'wyb', 88, 25, 'C'),(1009, 'spx', 72, 46, 'B'),(1010, 'yjj', 88, 16, 'B'),(1011, 'teg', 98, 18, 'A'),(1012, 'hxj', 61, 48, 'B')
) AS table_name(user_id, name, score, age, grades)
)-- CASE WHEN 的嵌套使用: 不同年龄段,成绩乘上不同系数之后,再判定等级(青年:1.0, 少年:1.0, 中年:1.1, 老年:1.2)
SELECT  user_id, name, score, age,CASE WHEN age >= 60 THEN CASE WHEN score*1.5 >= 90 THEN 'A'WHEN score*1.5 >= 80 AND score*1.5 < 90 THEN 'B'WHEN score*1.5 >= 60 AND score*1.5 < 80 THEN 'C'ELSE 'D' END WHEN age >= 40 AND age < 60 THEN CASE WHEN score*1.2 >= 90 THEN 'A'WHEN score*1.2 >= 80 AND score*1.2 < 90 THEN 'B'WHEN score*1.2 >= 60 AND score*1.2 < 80 THEN 'C'ELSE 'D' ENDWHEN age >= 20 AND age < 40 THEN CASE WHEN score*1.0 >= 90 THEN 'A'WHEN score*1.0 >= 80 AND score*1.0 < 90 THEN 'B'WHEN score*1.0 >= 60 AND score*1.0 < 80 THEN 'C'ELSE 'D' ENDELSE CASE WHEN score*1.0 >= 90 THEN 'A'WHEN score*1.0 >= 80 AND score*1.0 < 90 THEN 'B'WHEN score*1.0 >= 60 AND score*1.0 < 80 THEN 'C'ELSE 'D' ENDEND AS grades
FROM t_score_info
;