SQL基础教程6（3）和进阶教程1

CASE表达式：条件分支

• 表达式可以书写在任意位置

简单CASE表达式

--　简单CASE表达式
CASE <表达式>
　　 WHEN <表达式> THEN <表达式>
　　 WHEN <表达式> THEN <表达式>
 　　WHEN <表达式> THEN <表达式>
 　　. . .
　　 ELSE <表达式>
END

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

搜索CASE表达式

--　搜索CASE表达式
CASE WHEN <求值表达式> THEN <表达式>
 　　WHEN <求值表达式> THEN <表达式>
 　　WHEN <求值表达式> THEN <表达式>
 　　. . .
 　　ELSE <表达式>
END

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

注意事项：

1. 统一各分支返回的数据类型
2. 不要忘了写 END
3. 养成写 ELSE 子句的习惯，ELSE 子句可选，默认为 ELSE NULL，但最好明确地写上 ELSE 子

例题：

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

①

-- 把县编号转换成地区编号 (1)
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;


-- 把县编号转换成地区编号 ：将 CASE 表达式归纳到一处(用别名呀，MySQL可以顺利执行)
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;

②将数值按照适当的级别进行分类统计

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

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

将“行结构”的数据转换成了“列结构”的数据

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

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

某公司规定“女性员工的工资必须在 20 万日元以下”

“如果是女性员工，则工资是 20 万日元以下”：在命题逻辑中，该命题叫作蕴含式的逻辑表达式，记作 P → Q

CONSTRAINT check_salary CHECK
            ( CASE WHEN sex = '2'
                   THEN CASE WHEN salary <= 200000
                             THEN 1 ELSE 0 END
                   ELSE 1 END = 1 )

逻辑与也是一个逻辑表达式，意思是“P 且 Q”，记作 P ∧ Q

CONSTRAINT check_salary CHECK
         ( sex = '2' AND salary <= 200000 )

在 CHECK 约束里使用逻辑与：该公司将不能雇佣男性员工

使用蕴含式：男性也可以在这里工作

4、在 UPDATE 语句里进行条件分支

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

-- 用 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;

注：SQL 语句最后一行的 ELSE salary 非常重要，如果没有它，条件 1 和条件 2 都不满足的员工的工资就会被更新成 NULL。

5、表之间的数据匹配

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


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

6、在 CASE 表达式中使用聚合函数

1. 获取只加入了一个社团的学生的社团 ID
2. 获取加入了多个社团的学生的主社团 ID

用 CASE WHEN COUNT(*) = 1 …… ELSE …….这样的 CASE 表达式来表示 “只加入了一个社团还是加入了多个社团”的条件分支

-- 条件 1 ：选择只加入了一个社团的学生
SELECT std_id, MAX(club_id) AS main_club
  FROM StudentClub
 GROUP BY std_id
HAVING COUNT(*) = 1;

-- 条件 2 ：选择加入了多个社团的学生
SELECT std_id, club_id AS main_club
  FROM StudentClub
 WHERE main_club_flg = 'Y';

--用case表达式
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 AS main_club
  FROM StudentClub
 GROUP BY std_id;