SQL 查询：SELECT CASE 条件赋值
更新时间：2022-03-14 17:14:11 标签：sql
case
提示
Hive SQL 教程 编写中，使用过程中有任何建议，提供意见、建议、纠错、催更加微信 gairuo123。欢迎关注本教程作者新书《深入浅出Pandas：利用Python进行数据处理与分析》 。作者开办 Python 数据分析培训，详情 Python 数据分析培训。
查询出的结果可能是一个数值，或者我们要对它进行分类，就可以使用类似于 if else 的用法可以按条件进行分类翻译。
本文例子中使用的数据是筛选指定字段中的数据内容。
代码逻辑
select c_1,
CASE
WHEN condition1 THEN result1
WHEN condition2 THEN result2
WHEN conditionN THEN resultN
ELSE result_else
END as c_name
from tab_name
注：
- 由 CASE 开始 END 结束
- 最好起一个别名（as c_name，c_name 为别名），不然此列没有可读性名称
- WHEN 和 THEN 成对出现，WHEN 后边为条件，可以使用表中的所有字段，THEN 后为最终输出的值
- ELSE 为兜底逻辑，直接给出值，ELSE 可以没有
- 没有被条件覆盖的值为 null
案例
以下将班级 ID 翻译映射成中文名：
select class,
CASE
WHEN class = 1 THEN '一班'
WHEN class = 2 THEN '二班'
ELSE '三班'
END as c_name
from students
'''
class|c_name|
-----+------+
1|一班 |
2|二班 |
1|一班 |
2|二班 |
3|三班 |
1|一班 |
2|二班 |
1|一班 |
3|三班 |
'''
将性别去重并翻译成英文，为了减少 Case 中 gender 的代码，可以在 case 后直接跟 gender，然后在分支中判断值 ：
select distinct gender,
CASE gender
WHEN '男' THEN 'Male'
WHEN '女' THEN 'Female'
END as c_name
from students
'''
gender|c_name|
------+------+
男 |Male |
女 |Female|
'''
复杂条件，为了代码清晰可以把语句用括号括起来：
select name,
(CASE
WHEN chinese > 80 and math > 80 THEN '数学语文都好'
WHEN math > 80 THEN '数学好'
WHEN chinese > 80 THEN '语文好'
ELSE '不知道'
END) as review
from students
'''
name|review|
----+------+
张涛 |不知道 |
王琳 |数学语文都好|
赵丹丹 |不知道 |
李成 |语文好 |
赵天成 |不知道 |
田迪 |语文好 |
王卫栋 |数学好 |
周平 |语文好 |
武明 |不知道 |
'''
order by 应用
有时候用 order by 排序时，如果有空值想排到前边或者后边，或者给定一定的排序算法，可以将 CASE 语句用在 order by 中。
以下将生日小于 1990 年的同学数据加20分，再排序：
select name, b_year, math
from students
order by (CASE
WHEN b_year < 1990 THEN math+20
ELSE math
END) DESC
'''
name|b_year|math|
----+------+----+
王卫栋 | 1966| 88|
田迪 | 1988| 78|
武明 | 1977| 78|
周平 | 1988| 77|
王琳 | 2010| 88|
张涛 | 1950| 66|
赵天成 | 2000| 77|
赵丹丹 | 1996| 55|
李成 | 2011| 54|
'''
以下如果没有数学成绩，按语文成绩算，进行排名：
select name, math
from students
order by (CASE
WHEN math is null THEN chinese
ELSE math
END) DESC
'''
name|math|
----+----+
王琳 | 88|
王卫栋 | 88|
田迪 | 78|
武明 | 78|
赵天成 | 77|
周平 | 77|
张涛 | 66|
赵丹丹 | 55|
李成 | 54|
'''
实现分段统计
可以将 case 语句用在 sum 函数中，实现分段统计：
select sum(case when math > 80 then 1 else 0 end) as `大于90`,
sum(case when math < 60 then 1 else 0 end) as `小于60,`
sum(case when math <= 80 then 1 else 0 end) as `小于等于80`
from students
'''
大于90|小于60|小于等于80|
----+----+------+
2| 2| 7|
'''
注，上例中 as 后的字段名为一个虚拟列名，要符合 SQL 的列名规范，如果提示不符合，可加反引号（backquote）或者修改为不带中文字符的英文名。以上简单的 case when 可以用 if() 方法代替。
和 SUM 配合
我们知道，在统计统计数量时要用到 count 函数，但如果要分类统计时，可以用 case when 和 sum 配合，以下统计了男生女生分别的数量：
select
sum(case when gender = "男" then 1 else 0 end) as boy_qty,
sum(case when gender = "女" then 1 else 0 end) as girl_qty
from students
'''
boy_qty|girl_qty|
-------+--------+
6| 3|
'''
将要统计的分类映射为 1，不统计的为 0，sum 相加就是最终要统计的分类。
场景总结
CASE 语句经常用在对枚举值的翻译，因为在数据库中一般存储的是一个数值。还用在逻辑条件的赋值，赋值后再进行相应的计算处理。
