SQL 查漏补缺 CASE WHEN

简单Case表达式

Case sex 
when  '1'  then  '男'
when  '2'  then  '女'
else  '其他'  end

搜索Case表达式

Case when sex = '1' then '男'
	 when sex = '2' then '女'
	 else '其他' end

注意，在发现为真的when子句时，case表达式的真假值判断就会中止，而剩余的when子句会被忽略。使用when子句注意排他性。举例说明：

case when col_1 in ('a','b') then '第一'
	 when col_1 in ('a') then '第二'
	 else '其他' end

这样写，结果里不会出现’第二’。

将已有编号方式转换为新的方式并统计

现有表如下

需要以‘九州’、‘四国’等地区来统计人口总数，生成以下的表：

实现代码如下：
写法一：

select 
       case pref_name when '德岛' then '四国'
                      when '香川' then '四国' 
                      when '爱媛' then '四国'
                      when '高知' then '四国' 
                      when '福冈' then '九州'
                      when '佐贺' then '九州'
                      when '长崎' then '九州'
        else '其他'
        end as 地区名,
        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 pref_name 地区名, 
       sum(population) 人口
from (
select 
        case when pref_name in ('德岛','香川','爱媛','高知') then '四国'
             when pref_name in ('福冈','佐贺','长崎') then '九州'
        else '其他' end  pref_name  ,    
        population          
from poptbl ) p
group by p.pref_name

注意 : 写法一 的关键在于将select子句里的CASE表达式复制到group by子句里.

如果对转换前的pref_name进行group by, 就不会得到正确结果, 虽然不会报错.

同样地，也可以将数值按照适当的级别进行分类统计。例如，要按人口数量等级（pop_class ）查询都道府县个数的时候，就可以像下面这样写 SQL 语句。

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
FROM PopTbl
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     3
03     3
04     2

这个技巧非常好用。不过，必须在 SELECT 子句和 GROUP BY 子句这两处写一样的 CASE 表达式，这有点儿麻烦。后期需要修改的时候，很容易发生只改了这一处而忘掉改另一处的失误。

所以，如果我们可以像下面这样写，那就方便多了。

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 district; ←-------GROUP BY 子句里引用了SELECT 子句中定义的别名

用一条SQL语句进行不同条件的统计

进行不同条件的统计是CASE表达式的著名用法之一.

建表如下: poptbl2 (pref_name 县名, sex 性别 , population 人口)

SQL写法 :

select pref_name 县名,
       sum(case when sex = '1' then population else 0 end) 男,
       sum(case when sex = '2' then population else 0 end) 女  
from poptbl2 
group by pref_name

在update里进行条件分支

建表如下 : salaries (name 姓名, salary 薪水)

要求:

1. 对当前工资为30万元以上的员工, 降薪 10 %

2. 对当前工资为25万元以上 且 不满28万元的员工, 加薪 20 %

得到结果为 :

错误写法为 :

update salaries set salary = salary * 0.9 where salary >= 300000
update salaries set salary = salary * 1.2 where salary >= 250000 and salary < 280000 

正确写法：

update salaries set salary = case when salary >= 300000
                                  then salary * 0.9
                                  when salary >= 250000 and salary < 280000
                                  then salary *1.2
                             else salary end 

表之间的数据匹配

在case 表达式里, 我们可以使用 between , like 和 < > , in , exists 等谓词组合.
建表如下 : coursemaster (course_id 课程id , course_name 课程名 )

opencourses (month 月份, course_id 课程id )


要求得到：

SQL如下 :

select course_name,
       case when course_id in (select course_id from opencourses where month = 200706) then 'Y' else 'N' end "6月",
       case when course_id in (select course_id from opencourses where month = 200707) then 'Y' else 'N' end "7月",
       case when course_id in (select course_id from opencourses where month = 200708) then 'Y' else 'N' end "8月"  
from coursemaster

在CASE中使用聚合函数

建表如下: StudentClub (std_id 学号, club_id 社团id , club_name 社团名, main_club_flag 主社团标志)

表中存储了学生和社团多对多的关系, 假如一个学生有多个社团, 则其中一个为主社团标志位Y , 其余社团或只假如一个社团的学生的标志为N

要求 : 计算每个学生的主社团id

结果如下:

SQL如下:

select std_id 学号,
       case when count(*) = 1 then max(club_id) 
                              else max(case when main_club_flg = 'Y' then club_id else null end)
                              end 主社团id   
from studentclub
group by std_id 
order by std_id