SQL：CASE 表达式应用总结

多个 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

示例代码 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;

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

-- 男性人口 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;

例：限定所有女性员工的工资不能高于 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)

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）

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');

-- 表的匹配：使用 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;

-- 表的匹配：使用 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;

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

例：表 StudentClud，主键是 “学号、社团 ID”，其中有的学生加入了多个社团，有的学生只加入了一个社团，对于加入多个社团的学生，将其 “主社团标志” 设置为 Y 或者 N 来表明哪个社团是他的主社团；对于只加入一个社团的学生，将其 “主社团标志” 设置为 N；

需求：获取所有学生的学号，及其加入的主社团的社团 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;