1. 概述

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

1.2. 写法

  • 1.2.1. 简单CASE表达式

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

       CASE WHEN sex ='1'THEN’男’
           WHEN sex ='2'THEN’女’
       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.

   -- 男性人口
    SELECT pref_name,
          SUM(population)
      FROM PopTbl2
     WHERE sex ='1'
     GROUP BY pref_name;
    -- 女性人口
    SELECT pref_name,
          SUM(population)
      FROM PopTbl2
     WHERE sex ='2'
     GROUP BY pref_name;

2.2.

    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;

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

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

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

3.1.

CONSTRAINT check_salary CHECK

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

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

3.2.

CONSTRAINT check_salary CHECK

              ( sex ='2'AND salary <= 200000 )

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

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

4.1.

   --条件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;

4.2. 用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;
  • 4.2.1. 最后一行的ELSE salary非常重要,必须写上

  • 4.2.2. 因为如果没有它,条件1和条件2都不满足的员工的工资就会被更新成NULL

4.3.

   --1.将a转换为中间值d
    UPDATE SomeTable
      SET p_key ='d'
     WHERE p_key ='a';
    --2.将b调换为a
    UPDATE SomeTable
      SET p_key ='a'
      WHERE p_key ='b';
     --3.将d调换为b
     UPDATE SomeTable
        SET p_key ='b'
      WHERE p_key ='d';

4.4. 用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. 表之间的数据匹配

5.1. 表的匹配:使用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;

5.2.     表的匹配:使用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;
  • 5.2.1. 从性能方面来说,EXISTS更好

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

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

    SELECT std_id, MAX(club_id) AS main_club
      FROM StudentClub
     GROUP BY std_id
    HAVING COUNT(*) = 1;

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

    SELECT std_id, club_id AS main_club
      FROM StudentClub
     WHERE main_club_flg ='Y';

6.3.

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;

内容来源于网络如有侵权请私信删除

文章来源: 博客园

原文链接: https://www.cnblogs.com/lying7/p/17266357.html

你还没有登录,请先登录注册
  • 还没有人评论,欢迎说说您的想法!