读SQL进阶教程笔记01_CASE表达式

1. 概述

1.1. SQL-92标准里加入的最有用的特性
1.2. 写法

• 1.2.1. 简单CASE表达式
  1.    CASE sex
  2.      WHEN '1' THEN ’男’
  3.      WHEN '2' THEN ’女’
  4.    ELSE ’其他’ END

  复制代码
  
  
  • 1.2.1.1. 写法简单，但能实现的事情比较有限
    
  
  
• 1.2.2.     搜索CASE表达式
  1.    CASE WHEN sex ='1'THEN’男’
  2.        WHEN sex ='2'THEN’女’
  3.    ELSE ’其他’ END

  复制代码
  
  
  • 1.2.2.1. 简单CASE表达式能写的条件，搜索CASE表达式也能写
    
  • 1.2.2.2. 可以使用BETWEEN、LIKE和＜、＞等便利的谓词组合，以及能嵌套子查询的IN和EXISTS谓词
    
  • 1.2.2.3. CASE表达式用在SELECT子句里时，既可以写在聚合函数内部，也可以写在聚合函数外部
    

    1.2.2.3.1. CASE表达式在执行时会被判定为一个固定值，因此它可以写在聚合函数内部
    

    1.2.2.3.2. 因为它是表达式，所以还可以写在SELECE子句、GROUP BY子句、WHERE子句、ORDER BY子句里
    

    
    
  • 1.2.2.4. 是支撑SQL声明式编程的根基之一，也是灵活运用SQL时不可或缺的基础技能
    

    1.2.2.4.1. 在能写列名和常量的地方，通常都可以写CASE表达式
    

    
    
  
  

1.3. 注意事项

• 1.3.1. 统一各分支返回的数据类型
  
  
  
  • 1.3.1.1. 某个分支返回字符型，而其他分支返回数值型的写法是不正确的
    
  
  
• 1.3.2. 要注意条件的排他性
  
• 1.3.3. 不要忘了写END
  
• 1.3.4. 养成写ELSE子句的习惯
  
  
  
  • 1.3.4.1. ELSE子句是可选的
    
  • 1.3.4.2. 不写也不会出错
    

    1.3.4.2.1. 不写可能会造成“语法没有错误，结果却不对”这种不易追查原因的麻烦
    

    
    
  • 1.3.4.3. 即便是在结果可以为NULL的情况下
    
  
  

1.4. 必须在SELECT子句和GROUP BY子句这两处写一样的CASE表达式
1.5. GROUP BY子句使用的正是SELECT子句里定义的列的别称

• 1.5.1. 这种写法是违反标准SQL的规则的
  
  
  
  • 1.5.1.1. 因为GROUP BY子句比SELECT语句先执行
    
  • 1.5.1.2. 所以在GROUP BY子句中引用在SELECT子句里定义的别称是不被允许的
    
  
  
• 1.5.2. Oracle、DB2、SQL Server等数据库里采用这种写法时就会出错
  
• 1.5.3. 在PostgreSQL和MySQL中可以顺利执行
  
• 1.5.4. 不强烈推荐大家使用
  

2. 示例

2.1.

1.    -- 男性人口
2.     SELECT pref_name,
3.           SUM(population)
4.       FROM PopTbl2
5.      WHERE sex ='1'
6.      GROUP BY pref_name;
7.     -- 女性人口
8.     SELECT pref_name,
9.           SUM(population)
10.       FROM PopTbl2
11.      WHERE sex ='2'
12.      GROUP BY pref_name;

复制代码

2.2.

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

复制代码

2.3. 能将SQL的查询结果转换为二维表的格式

• 2.3.1. 新手用WHERE子句进行条件分支，高手用SELECT子句进行条件分支
  

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

3.1.

1. CONSTRAINT check_salary CHECK
3.              ( CASE WHEN sex ='2'
4.                      THEN CASE WHEN salary ＜= 200000
5.                              THEN 1 ELSE 0 END
6.                      ELSE 1 END = 1 )

复制代码

• 3.1.1. 使用蕴含式，男性也可以在这里工作
  

3.2.

1. CONSTRAINT check_salary CHECK
3.              ( sex ='2'AND salary ＜= 200000 )

复制代码

• 3.2.1. 使用逻辑与，该公司将不能雇佣男性员工
  

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

4.1.

1.    --条件1
2.     UPDATE Salaries
3.       SET salary = salary ＊ 0.9
4.      WHERE salary ＞= 300000;
5.     --条件2
6.     UPDATE Salaries
7.       SET salary = salary ＊ 1.2
8.      WHERE salary ＞= 250000 AND salary ＜ 280000;

复制代码

4.2. 用CASE表达式写正确的更新操作

1.    UPDATE Salaries
2.      SET salary = CASE WHEN salary ＞= 300000
3.                        THEN salary ＊ 0.9
4.                        WHEN salary ＞= 250000 AND salary ＜ 280000
5.                        THEN salary ＊ 1.2
6.                        ELSE salary END;

复制代码

• 4.2.1. 最后一行的ELSE salary非常重要，必须写上
  
• 4.2.2. 因为如果没有它，条件1和条件2都不满足的员工的工资就会被更新成NULL
  

4.3.

1.    --1．将a转换为中间值d
2.     UPDATE SomeTable
3.       SET p_key ='d'
4.      WHERE p_key ='a';
5.     --2．将b调换为a
6.     UPDATE SomeTable
7.       SET p_key ='a'
8.       WHERE p_key ='b';
9.      --3．将d调换为b
10.      UPDATE SomeTable
11.         SET p_key ='b'
12.       WHERE p_key ='d';

复制代码

4.4. 用CASE表达式调换主键值

1.    UPDATE SomeTable
2.      SET p_key = CASE WHEN p_key ='a'
3.                        THEN 'b'
4.                        WHEN p_key ='b'
5.                        THEN 'a'
6.                        ELSE p_key END
7.     WHERE p_key IN ('a', 'b');

复制代码

5. 表之间的数据匹配

5.1. 表的匹配：使用IN谓词

1.    SELECT course_name,
2.          CASE WHEN course_id IN
3.                        (SELECT course_id FROM OpenCourses
4.                          WHERE month = 200706) THEN'○'
5.                ELSE'×'END AS "6月",
6.          CASE WHEN course_id IN
7.                        (SELECT course_id FROM OpenCourses
8.                          WHERE month = 200707) THEN'○'
9.                ELSE'×'END AS "7月",
10.          CASE WHEN course_id IN
11.                        (SELECT course_id FROM OpenCourses
12.                          WHERE month = 200708) THEN'○'
13.                ELSE'×'END  AS "8月"
14.      FROM CourseMaster;

复制代码

5.2.     表的匹配：使用EXISTS谓词

1.    SELECT CM.course_name,
2.          CASE WHEN EXISTS
3.                        (SELECT course_id FROM OpenCourses OC
4.                          WHERE month = 200706
5.                              AND OC.course_id = CM.course_id) THEN'○'
6.                  ELSE'×'END AS "6月",
7.              CASE WHEN EXISTS
8.                          (SELECT course_id FROM OpenCourses OC
9.                            WHERE month = 200707
10.                              AND OC.course_id = CM.course_id) THEN'○'
11.                  ELSE'×'END AS "7月",
12.              CASE WHEN EXISTS
13.                          (SELECT course_id FROM OpenCourses OC
14.                            WHERE month = 200708
15.                              AND OC.course_id = CM.course_id) THEN'○'
16.                  ELSE'×'END  AS "8月"
17.        FROM CourseMaster CM;

复制代码

• 5.2.1. 从性能方面来说，EXISTS更好
  

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

6.1. 条件1：选择只加入了一个社团的学生

1.    SELECT std_id, MAX(club_id) AS main_club
2.      FROM StudentClub
3.     GROUP BY std_id
4.    HAVING COUNT(＊) = 1;

复制代码

6.2. 条件2：选择加入了多个社团的学生

1.    SELECT std_id, club_id AS main_club
2.      FROM StudentClub
3.     WHERE main_club_flg ='Y';

复制代码

6.3.

1. SELECT  std_id,
2.             CASE WHEN COUNT(＊) = 1  --只加入了一个社团的学生
3.                 THEN MAX(club_id)
4.                 ELSE MAX(CASE WHEN main_club_flg ='Y'
5.                               THEN club_id
6.                               ELSE NULL END)
7.             END AS main_club
8.       FROM StudentClub
9.      GROUP BY std_id;

复制代码

免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！