sql之case的神奇用法

摘录了一些我平常不太会用到的写法【以下内容都是在MySQL数据库执行的】

1、如果不写else，默认返回null

假设表sometable的内容如下

1.PNG

如果p_key='a'，则返回AA；如果p_key='b'，则返回BB。
不写else：

select 
p_key,
case p_key
when 'a' then 'AA'
when 'b' then 'BB'
end as p_value
from sometable;
-- 或者
select 
p_key,
case 
when p_key='a' then 'AA'
when p_key='b' then 'BB'
end as p_value
from sometable;

返回

2.PNG

2、 聚合函数和case表达式结合使用

• 例1：聚合函数里面使用case表达式
  假设表poptbl2的内容如下

  
  
  3.PNG
  
  

  需求：希望统计每个县的男生和女生数量

select 
p.pref_name,
sum(case when p.sex=1 then p.population else 0 end) num_male,
sum(case when p.sex=2 then p.population else 0 end) num_female
from poptbl2 p
GROUP BY p.pref_name

上面这种写法将聚合函数和case表达式结合起来使用，且能将行结构转化成列结构。

• 例2：在case表达式里面使用聚合函数
  假设表studentclub内容如下

  
  
  11.PNG
  
  

  对于加入了一个社团的学生，将其主社团标志main_club_flg设为N；
  对于加入了多个社团的学生，将其主社团标志main_club_flg设为Y，该社团作为该学生的主社团。
  需求：
       （1）获取只加入了一个社团的学生的社团ID
       （2）获取加入了多个社团的学生的主社团ID

select 
s.std_id,
case
when count(s.std_id)=1 then max(s.club_id)
else max(case
          when s.main_club_flg='Y' then s.club_id
                    else null END) 
end as main_club_id
from studentclub s
GROUP BY s.std_id

返回

12.PNG

3、update和case表达式结合使用

• 例1：根据字段值不同来更新该字段的值
  假设表salaries的内容如下

  
  
  4.PNG
  
  

  需求：
       （1）对当前工资为30万元以上的员工，降薪10%
       （2）对当前工资为25万元以上且不满28万日元的员工，加薪20%
       （3）不满足以上条件的员工，薪资保持不变

update salaries s
set s.salary=(case when s.salary>=300000 then s.salary*0.9
                  when s.salary>=250000 and s.salary<280000 then s.salary*1.2
             else s.salary
             end);

现在salaries的内容变为

5.PNG

• 例2：调换主键值
  假设表sometable的内容如下

  
  
  6.PNG
  
  

  需求：调换主键p_key中a和b的位置

update sometable s
set s.p_key=(case 
              when s.p_key='a' then 'b'
              when s.p_key='b' then 'a'
              else s.p_key
              end)
where s.p_key in ('a','b');

这种写法当p_key是主键时，在MySQL中会报错

7.PNG

4、case表达式中嵌套子查询

假设表coursemaster内容如下

8.PNG

假设表opencourses内容如下

9.PNG

select 
c.course_name,
case
when c.course_id in (select p.course_id from opencourses p where p.month_date='200706') then 'Y'
else 'N' end as '6月',
case
when c.course_id in (select p.course_id from opencourses p where p.month_date='200707') then 'Y'
else 'N' end as '7月',
case
when c.course_id in (select p.course_id from opencourses p where p.month_date='200708') then 'Y'
else 'N' end as '8月'
from coursemaster c
-- 或者
select 
c.course_name,
case
when EXISTS(select p.course_id from opencourses p where p.month_date='200706' and c.course_id=p.course_id) then 'Y'
else 'N' end as '6月',
case
when EXISTS(select p.course_id from opencourses p where p.month_date='200707' and c.course_id=p.course_id) then 'Y'
else 'N' end as '7月',
case
when EXISTS(select p.course_id from opencourses p where p.month_date='200708' and c.course_id=p.course_id) then 'Y'
else 'N' end as '8月'
from coursemaster c

返回

10.PNG