目录
一:case when 两种基础用法
方法一:
case when 字段 满足条件一( eg: = 、>、like) then 结果1
when 字段 满足条件二 then 结果2
else 结果3 end
方法二:
case 字段
when 值1 then 结果1
when 值2 then 结果2
else 结果3 end
二:两种基础用法如何选择
- 如果需要判断的条件是枚举值,用“=”进行判断,建议使用第二种方法,简洁明了,少写代码。(方法一也可以实现)
select
case sex
when '1' then '男'
when '2' then '女'
else ' 未知' end
as sex_desc
from table_sex
- 如果需要判断的条件不是等号,例如是> 、>=、like等这种判断逻辑的,我们只能使用第一种繁琐一点的方法。(方法二实现不了)
# 样例1
select
case when grade > 60 and grade <70 then '及格'
case when grade >= 70 and grade <90 then '良好'
case when grade >= 90 then '优秀'
else '不及格' end
as grade_desc
from table_grade
# 样例2
select
case when name like '叶%' then '姓叶的人'
case when name like '王%' then '姓王的人'
else '既不是姓叶也不是姓王的人' end
as name_desc
from table_name
三:使用注意事项
3.1 then后面不能接子查询
then的后面不能接查询语句。
with tmp as(
select 'yw' as name,
'男' as sex
union
select 'wc' as name,
'女' as sex
)
select
case when name = 'yw' then (select sex from tmp where name = 'yw')
when name = 'wc' then (select sex from tmp where name = 'wc')
else ' 未知' end as new_sex
from tmp
3.2 then后面是不是只能是结果值
then的后面只能接具体的值,不能接逻辑表达式
with table_name
(
select 'yh' as name,
'女' as sex
)
select case
when name = 'yw' then sex = '男'
when name = 'wc' then sex = '女'
else '未知' end as new_sex
from table_name
3.3 then后面能不能接两列
then的后面不能接两列的值,如果想要接两列的值,就必须写两遍case when then逻辑。
四:如何优化接两列的情况
case when的then后面如果有两列逻辑需要处理,我们必须写两次case when then逻辑,每一次对应一列的逻辑即可。
方法一:常规方法:两次case when then逻辑
with table_name
(
select
001 as id,
'yh' as name,
'女' as sex,
25 as age
union
select
003 as id,
'yw' as name,
'女' as sex,
26 as age
)
select id,
case
when name = 'yw' then '男'
when name = 'wc' then '女'
else '未知' end as new_sex,
--
case name
when 'yw' then 18
when 'wc' then 16
else 33 end as new_age
from table_name
方法二:先把then后面的两列放在对应一个表存储着,我们使用join来进行连接,连接条件就是when后面的对应条件。
因为方法一有两次case when条件匹配,效率可能不高。
with table_name
(
select 001 as id,
'yh' as name,
'女' as sex,
25 as age
union
select 003 as id,
'yw' as name,
'女' as sex,
26 as age
),
# 迭代更新信息后的表
table_new(
select 'yw' as name,
'男' as sex,
18 as age
union
select 'yh' as name,
'男' as sex,
16 as age
)
select
t1.id,
t1.name,
t2.sex,
t2.age
from table_name t1
join table_new t2
on t1.name = t2.name #on的后面可以用等值连接,也可以使用like这些进行匹配连接