当前位置:首页 > 关注 > > 正文

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

来源:博客园    时间:2023-03-29 07:41:49

1.概述

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

1.2.写法


(相关资料图)

  • 1.2.1.简单CASE表达式

    CASE sexWHEN "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;

关键词:

上一篇:

下一篇:

精心推荐

X 关闭

X 关闭