SQL：CASE 表达式应用总结

一、CASE 表达式概述

  • 逻辑：如果满足 WHEN 后的条件，返回 THEN 后的内容；
    • WHEN 后的条件：可以使用 BETWEEN、LIKE、<、>、= 等谓词组合，也可以嵌套子查询的 IN 和 EXISTS 谓词；

 

1、两种写法：简单 CASE 表达式、搜索 CASE 表达式

  • -- 简单 CASE 表达式
CASE sex
    WHEN '1' THEN '男'
    WHEN '2' THEN '女'
ELSE '其他' END
  • -- 搜索 CASE 表达式
CASE WHEN sex = '1' THEN '男'
     WHEN sex = '2' THEN '女'
ELSE '其他' END

-- 将 sex 列中，如果数值为 '1'，返回 '男'，如果数值为 '2'，返回 '女'，所有不是 '1' 也不是 '2'，返回 '其他'；

     

  • 两种表达式执行结果相同：“sex” 字段如果是 “1”，那么结果是 “男”；如果是 “2”，那么结果是 “女”；
  • 简单 CASE 表达式能写的条件，搜索 CASE 表达式也能写；

 

 

2、注意事项

  1. 多个 WHEN 子句的条件之间，不要有交集

    • 在 SQL 语句中，当 WHEN 子句为真时，CASE 表达式的真假值判断就会中止，而剩余的 WHEN 子句会被忽略；因此，为了避免引起混乱，使用 WHEN 子句时要注意条件的排他性；
      • 翻译：当一个 WHEN 子句的条件包含了其它 WHEN 子句的条件，那么被包含的 WHEN 子句不会被执行；
    • -- 例：这样写的话，结果里不会出现 “第二”
CASE WHEN col_1 IN ('a', 'b') THEN '第一'
     WHEN col_1 IN ('a') 　　  THEN '第二'
ELSE '其他' END

  2. 所有分支返回的数据类型要一致

    • 理解：所有的 WHEN 后的条件中的数据的类型，要与所有的 THEN 后需要转换的新数据的类型要一致；
      • 如，('a', 'b')、'第一'、('a')、'第二'，这些数据的类型都要一致；

  3. 不要忘记 END

  4. 养成写 ELSE 子句的习惯

    • 与 END 不同，ELSE 子句是可选的；如果不写 ELSE 子句，CASE 表达式的执行结果是 NULL，可能会造成 “语法没有错误，结果缺却不对” 的情况，而且这种情况不易追查原因，所以最好写上 NULL（即便结果可以是 NULL）；

 

 

二、各种问题场景下的应用

1、场景一：将已有编号方式转换为新的方式并统计

  • 例：统计表 PopTbl 中内容，按地区分类统计显示；（表中没有地区字段，需要创建地区字段并对县名字段分类属于哪个地区）

    1. 示例代码 1：把县编号转换为地区编号

      • 注意：GROUP BY 后要跟 CASE 的表达式，尽量不要跟表达式的别名 district，因为 GROUP BY 子句比 SELECT 子句先执行，如果使用了别名则违反了标准的 SQL 规则；（但是在 MySQL 和 PostgreSQL 中可以使用别名，而在 SQL Server、Oracle、DB2 中不可以使用别名；）
      • -- 把县编号转换为地区编号
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 CASE pref_name
             WHEN '德岛' THEN '四国'
             WHEN '香川' THEN '四国'
             WHEN '爱媛' THEN '四国'
             WHEN '高知' THEN '四国'
             WHEN '福冈' THEN '九州'
             WHEN '佐贺' THEN '九州'
             WHEN '长崎' THEN '九州'
         ELSE '其他' END;

    2. 示例代码 2：按人口数量等级划分都道府县

      • -- 按人口数量等级划分都道府县
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
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       　　 2
03        　　3
04       　　 2

 

 

2、场景二：用一条 SQL 语句进行不同条件的统计

  • 例：从 PopTbl 表中，按性别汇总各县的人口

    1. 示例代码 1：使用 CASE 表达式实现

      • SELECT  pref_name,
        -- 男性人口
        SUM(CASE WHEN sex = '1' THEN population) AS cnt_m,
        SUM(CASE WHEN sex = '2' THEN population) AS cnt_f
FROM    PopTbl2
GROUP BY pref_name;

    2. 示例代码 2：使用 UNION 连接多个 SELECT

      • 注：不过这种写法，结果集中不是 3 列，而是 2 列，不是 8 行数据，而是 16 行数据；
      • SELECT   pref_name,
         SUM(population) AS cnt_m
FROM     PopTbl
WHERE    sex = '1'
GROUP BY pref_name;
UNION
SELECT   pref_name,
         SUM(population) AS cnt_f
FROM     PopTbl
WHERE    sex = '2'
GROUP BY pref_name;

    3. 示例代码 3：使用 JOIN 实现

      • -- 该代码仅做参考；（因为我也不知道自己写的能不能执行）
SELECT  p1.pref_name, 
        p1.cnt_m, 
        p2.cnt_f
FROM    (
            SELECT    pref_name,
            　　　　　 SUM(population) AS cnt_m
            FROM    　PopTbl
            WHERE     sex = '1'
            GROUP BY  pref_name
        ) AS p1
        INNER JOIN
        (
            SELECT    pref_name,
            　　　　   SUM(population) AS cnt_f
            FROM      PopTbl
            WHERE     sex = '2'
            GROUP BY  pref_name
        ) AS p2
        ON p1.pref_name = p2.pref_name;

         

 

3、场景三：用 CHECK 约束定义多个列的条件关系

  • 例：限定所有女性员工的工资不能高于 20 万
    • 注：此处嵌套了 CASE 表达式，用于同时满足多种条件的时候；（如，此例中必须同时满足：sex = '2'、salary <= 200000）
    • 另外：添加 CHECK 约束时，如果不是针对多有情况的值设定校验条件，不能使用 AND；比如此例中，如果使用（sex = '2' AND salary <= 200000），则表示只允许插入 sex = ‘2’ 并且不高于 200000 的薪水；
    • -- CASE 表达式是可以嵌套的，进行多条件刷选
CONSTRINT   check_salary CHECK
            (CASE WHEN sex = '2'
                    THEN CASE WHEN salary <= 200000
                                THEN 1 ELSE 0 END
                  ELSE 1 END 1)

 

 

4、场景四：在 UPDATE 语句里进行条件分支

  • 示例需求 1：

    1. 对当前工资为 30 万日元及以上的员工，降薪 10%；
    2. 对当前工资为 25 万日元以上且不满 28 万日元的员工，加薪 20%；

 

  • 错误代码：使用两条 UPDATE 语句更新；
    • 病因：大于 30 万的薪水，会经过两次 UPDATE；
    • -- 条件 1
UPDATE    Salaries
   SET    salary = salary * 0.9
 WHERE    salary > 300000;

 -- 条件 2
 UPDATE    Salaries
    SET    salary = salary * 1.2
  WHERE    salary >= 250000 AND salary <= 280000;

 

  • 正确代码：使用 CASE 表达式 
    • 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;
    • 注：ELSE 后要返回原数据，即不再条件范围内的员工的薪水不变；（如果没有 ELSE ，则会将 salary 中不满足 WHEN 条件的值更新为 NULL）

 

  • 示例需求 2：更换数据；（将表 SomeTable 中 p_key 列的 ‘a’ 值和 ‘b’ 互换）

    • 代码 1：使用 3 条 UPDATE 语句 
      • -- 1、将 a 转为中间值 c
UPDATE    SomeTable
   SET    p_key = 'c'
 WHERE    p_key = 'a';

-- 2、将 b 转为 a
 UPDATE    SomeTable
    SET 　　p_key = 'a'
  WHERE    p_key = 'b';

-- 3、将 c 调换为 b
UPDATE    SomeTable
   SET    p_key = 'b'
 WHERE    p_key = 'c';
    • 代码 2：使用 CASE 表达式 
      • UPDATE    SomeTable
   SET    p_key = CASE WHEN p_key = 'a' THEN 'b'
                       WHEN p_key = 'b' THEN 'a'
                       ELSE p_key END
 WHERE    p_key IN ('a', 'b');

 

 

5、场景五：表之间的数据匹配

  • WHEN 后的条件中嵌套子查询的 IN 和 EXISTS 谓词
  • 需求：根据 CourseMaster 表和 OpenCourse 表生产下图交叉表

    1. 表匹配：使用 IN 谓词

      • -- 表的匹配：使用 IN 谓词
SELECT  course_name,
        CASE WHEN course_id IN ( SELECT course_id FROM OpenCourse
                                   WHERE month = 200706) THEN '○' 
             ELSE '×' END AS '6月'，
        CASE WHEN course_id IN ( SELECT course_id FROM OpenCourse
                                  WHERE month = 200707) THEN '○' 
             ELSE '×' END AS '7月'，
        CASE WHEN course_id IN ( SELECT course_id FROM OpenCourse
                                  WHERE month = 200708) THEN '○'
             ELSE '×' END AS '8月'
  FROM  CourseMaster;

    2. 表匹配：使用 EXISTS 谓词

      • -- 表的匹配：使用 EXISTS 谓词
SELECT  CM.course_name,
        CASE WHEN EXISTS ( SELECT OC.course_id FROM OpenCourse AS OC
                            WHERE month = 200706
                              AND OC.course_id = CM.course_id) THEN '○' 
             ELSE '×' END AS "6月"，
        CASE WHEN EXISTS ( SELECT OC.course_id FROM OpenCourse AS OC
                            WHERE month = 200707
                              AND OC.course_id = CM.course_id) THEN '○' 
             ELSE '×' END AS "7月"，
        CASE WHEN EXISTS ( SELECT OC.course_id FROM OpenCourse AS OC
                            WHERE month = 200708
                              AND OC.course_id = CM.course_id) THEN '○' 
             ELSE '×' END AS "8月"，
  FROM  CourseMaster AS CM;

    • 两种方法对比：

      • 使用 EXISTS 性能更佳，特别是当表 OpenCourse 里的数据量较多的时候；
      • 原因：使用 EXISTS 进行的子查询能够用到 “month，course_id” 这样的主键索引，查询速度更快；

 

 

6、场景六：在 CASE 表达式中使用聚合函数

  • 例：表 StudentClud，主键是 “学号、社团 ID”，其中有的学生加入了多个社团，有的学生只加入了一个社团，对于加入多个社团的学生，将其 “主社团标志” 设置为 Y 或者 N 来表明哪个社团是他的主社团；对于只加入一个社团的学生，将其 “主社团标志” 设置为 N；
    • 需求：获取所有学生的学号，及其加入的主社团的社团 ID； 
      • SELECT std_id,
       CASE WHEN COUNT(*) = 1    -- 只加入了一个社团的学生
            THEN club_id
            ELSE CASE WHEN main_club_flg = 'Y'
                      THEN club_id
                      ELSE NULL END 
       END AS main_club
  FROM StudentClub
 GROUP BY std_id;

 

 

三、综述

  • CASE 表达式真的很强大：

    • 作为表达式，CASE 表达式在执行时会被判定为一个固定值，因此它可以写在聚合函数内部；也正是因为它是表达式，所有还可以写在 SELECT 子句、GROUP BY 子句、WHERE 子句、ORDER BY 子句里；简单点说，能写列名和常量的地方，通常都可以写 CASE 表达式。
  • CASE 表达式不依赖于具体的数据库的技术，可以在所有 DBMS 间移植；
  • 新手用 WHERE / HAVING 子句进行条件分支，高手用 SELECT 子句进行条件分支；

 

posted @ 2020-09-28 16:17  何永灿  阅读(787)  评论(0)  编辑  收藏  举报