SQL技巧之CASE - WHEN表达式

参考书籍: SQL进阶教程

目录

CASE基本语法及作用:

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

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

在update里进行条件分支

表之间的数据匹配

在CASE中使用聚合函数

多列数据的最大值

自定义排序

---

CASE基本语法及作用:

• 条件分支
• 行转列

标准写法 : case sex when '1' then '男' 

                                 when '2' then '女'

                  else '其他'  end

          或者 : 

                  case when sex = '1' then '男'

                           when sex = '2' then '女'

                  else '其他'  end

注意 : 当发现为真的when 子句时, case 表达式的真假值判断就会终止, 而剩余的when 子句就会被忽略.

          使用when子句时注意条件的排他性.

类似函数 :  decode(Oracle) , if(MySQL)

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

建表如下 : poptbl (pref_name 县名,population 人口)

要求得到如下格式信息:

写法一: 

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, 就不会得到正确结果, 虽然不会报错.

用一条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 

表之间的数据匹配

与decode 相比 , case 表达式具有非常强大的表达能力. 在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

注意 :  我们在初学SQL的时候, 都学过对聚合结果进行条件判断时要用 having 子句,  但是这里我们在 select 子句中使用case 表达式也可以达到同样的效果.  如果用一句话形容这种技巧, 可以这样说 : 

新手用having子句进行条件分支, 高手用select子句进行条件分支.

通过这条SQL我们可以知道, case 表达式用在select 子句里时, 既可以写在聚合函数内部, 也可以写在聚合函数外部. 这种高度自由的写法正是表达式的魅力所在.

case表达式可以写在 select子句, group by子句, where子句, order by 子句里. 简单点说, 在能写列名和常量的地方, 通常都可以写case表达式.

多列数据的最大值

建表如下:  greatests

要求1 : 求 x 与 y 中的最大值

结果如下:

SQL如下:

select key,
       case when x > y then x else y end greatest 
from greatests

要求2 : 求 x , y , z 三列最大值

分析 : 可以按照上面的解法使用case when 继续分支下去, 不过如果要比较的列很多, 就不太适合了. 下面介绍一种通用写法(不使用函数, 而是使用标准SQL的方式类实现)

SQL如下 : 

select key, max(col) as greatests
  from (select key, x as col
          from greatests
        union all
        select key, y as col
          from greatests
        union all
        select key, z as col
          from greatests) tmp
 group by key
 order by key

自定义排序

建表如下 : 

要求 : 上表中是对 key属性 进行order by后的结果, 现在要求 按照 B - A - C - D 这样的自定义顺序输出.

结果如下:

SQL如下:

select key,x
from greatests
order by case key when 'B' then 1
                  when 'A' then 2
                  when 'C' then 3
                  when 'D' then 4
         else null end  

或者 (只输出key顺序)

select key, case key when 'B' then 1 
                     when 'A' then 2
                     when 'C' then 3
                     when 'D' then 4
            else null end as sort_col
from greatests  
order by sort_col