ToB企服应用市场:ToB评测及商务社交产业平台

标题: SQL进阶 | CASE表达式 [打印本页]

作者: 徐锦洪 时间: 2024-6-15 01:31
标题: SQL进阶 | CASE表达式
本文所有案例基于《SQL进阶教程》实现。
概述

SQL中的CASE表达式是一种通用的条件表达式，类似于其他语言中的if/else语句。它用于在SQL语句中实现条件逻辑。CASE表达式以WHEN子句开始，后面跟着一个或多个WHEN条件，每个WHEN条件后面跟着一个THEN子句。假如任何WHEN条件为真，则返回相应的THEN子句中的表达式。假如没有任何WHEN条件为真，则可以选择性地利用ELSE子句来指定一个默认的表达式。
CASE表达式的语法如下：

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

复制代码

必要留意，在发现为真的 WHEN 子句时，CASE 表达式的真假值判定就会停止，而剩余的 WHEN 子句会被忽略。为了制止引起不须要的混乱，利用 WHEN 子句时要留意条件的排他性。

-- 例如，这样写的话，结果里不会出现“第二”
CASE WHEN col_1 IN ('a', 'b') THEN '第一'
WHEN col_1 IN ('a') THEN '第二'
ELSE '其他' END

复制代码

别的，利用 CASE 表达式的时候，还必要留意以下几点。

统一各分支返回的数据范例
不要忘了写 END
养成写 ELSE 子句的习惯

效果转化

例如，如今有一张按照“‘1：北海道’、‘2：青森’、……、‘47：冲绳’”
这种编号方式来统计都道府县 A 人口的表，我们必要以东北、关东、九州等地区为单位来分组，并统计人口数量。具体来说，就是统计下表 PopTbl中的内容，得出如右表“统计效果”所示的效果。

代码如下：

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 子句里引用了 SELECT 子句中定义的别名
GROUP BY district;

复制代码

利用case表达式可以或许方便的将数据库中查询到的效果转化为我们必要的效果，但是在本代码中利用到的别名进行分组，这种写法是违反尺度sql的规则的。在select语句的实行流程中，group by语句会比select语句先实行，所以在group by语句中引用在select语句里界说的别称是不被允许的。
条件统计

例如，我们必要往存储各县人口数量的表 PopTbl 里添加上“性别”列，然后求按性别、县名汇总的人数。具体来说，就是统计表 PopTbl2 中的数据，然后求出如表“统计效果”所示的效果。

代码如下：

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;

复制代码

配合check束缚利用

假设某公司规定“女性员工的工资必须在 20 万日元以下”，而在这个公司的人事表中，这条无理的规定是利用 CHECK 束缚来描述的，代码如下所示。

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

复制代码

        代码1表示的寄义是：限定插入“假如员工的性别为女，在此基础上判定工资是否在20万日元以下”的数据，假如员工不是女性，则不做限定。
        代码2表示的寄义是：限定插入“员工必须为女性而且工资必须在20万日元以下”的数据。
所以代码1表示的寄义才是我们所需求的，这就体现出与case与check配合的独特性了。
在update语句进行条件分支

        需求：以某数值型的列的当前值为判定对象，将其更新成别的值。这里的标题是，此时UPDATE操作的条件会有多个分支。例如，我们通过下面这样一张公司人事部的员工工资信息表 Salaries 来看一下这种环境。

假设如今必要根据以下条件对该表的数据进行更新。
1. 对当前工资为 30 万日元以上的员工，降薪 10%。
2. 对当前工资为 25 万日元以上且不满 28 万日元的员工，加薪 20%。按照这些要求更新完的数据应该如下表所示。

代码如下：

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

复制代码

代码1利用了2条update语句，分别对这两种条件进行修改，先更新工资大于30万日元的数据，再更新25-28万日元的数据，这就会导致第一次更新之后，相田的工资已经被更新成25-28万日元之间了，第二次继承更新，影响了终极效果。所以这种更新方式不可取。
代码2利用了case条件进行更新，这种利益是只实行1次sql，效率更高，且对数据更安全。
数据匹配

如下所示，这里有一张资格培训学校的课程一览表和一张管理每个月所设课程的表。

我们要用这两张表来天生下面这样的交错表，以便于一目了然地知道每个月开设的课程。

代码如下：

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

复制代码

         这样的查询没有进行聚合，因此也不必要排序，月份增长的时候仅修改 SELECT 子句就可以了，扩展性比较好。
        无论利用 IN 还是 EXISTS，得到的效果是一样的，但从性能方面来说，EXISTS 更好。通过 EXISTS 进行的子查询可以或许用到“month, course_id”这样的主键索引，因此尤其是当表 OpenCourses 里数据比较多的时候更有优势。
利用聚合函数

        假设这里有一张表现了门生及其加入的社团的一览表。如表 StudentClub 所示，这张表的主键是“学号、社团 ID”，存储了门生和社团之间多对多的关系。

有的门生同时加入了多个社团（如学号为 100、200 的门生），有的门生只加入了某一个社团（如学号为 300、400、500 的门生）。对于加入了多个社团的门生，我们通过将其“主社团标志”列设置为 Y 大概 N 来表明哪一个社团是他的主社团；对于只加入了一个社团的门生，我们将其“主社团标志”列设置为 N。
接下来，我们按照下面的条件查询这张表里的数据。
1. 获取只加入了一个社团的门生的社团 ID。
2. 获取加入了多个社团的门生的主社团 ID。

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;

复制代码

利用CASE 表达式表示了“只加入了一个社团还是加入了多个社团”这样的条件分支。假如只加入一个社团就获取社团id，假如加入多个社团就获取主社团id。
总结

在 GROUP BY 子句里利用 CASE 表达式，可以机动地选择作为聚合的单位的编号或等级。这一点在进行非定制化统计时能发挥巨大的威力。
在聚合函数中利用 CASE 表达式，可以轻松地将行结构的数据转换成列结构的数据。
相反，聚合函数也可以嵌套进 CASE 表达式里利用。
相比依赖于具体数据库的函数，CASE 表达式有更强大的表达能力和更好的可移植性。
正由于 CASE 表达式是一种表达式而不是语句，才有了这诸多长处。

练习题

1.用 SQL 从多行数据里选出最大值或最小值很轻易——通过 GROUP BY子句对合适的列进行聚合操作，并利用 MAX 或 MIN 聚合函数就可以求出。那么，从多列数据里选出最大值该怎么做呢？

代码如下：

select gkey,
case when x > y then (case when x > z then x else z end)
else (case when y > z then y else z end) end as greatest
from greatests

复制代码

2.利用正文中的表 PopTbl2 作为样本数据，练习一下把行结构的数据转换为列结构的数据吧。这次请天生下面这样的表头里带有汇总和再揭的二维表。

代码如下：

select
case sex when 1 then '男' else '女' end as '性别',
sum(population) as '全国',
sum(case when pref_name = '德岛' then population else 0 end) as '德岛',
sum(case when pref_name = '香川' then population else 0 end) as '香川',
sum(case when pref_name = '爱媛' then population else 0 end) as '爱媛',
sum(case when pref_name = '高知' then population else 0 end) as '高知',
sum(case when pref_name in ('德岛','香川','爱媛','高知') then population else 0 end) as '四国（再揭）'
from poptbl2
group by sex

复制代码

3.对练习题 1 里用过的表 Greatests 正常实行 SELECT key FROM Greatests ORDER BY key; 这个查询后，效果会按照 key 这一列值的字母表顺序表现出来。
那么，请思考一个查询语句，使得效果按照 B-A-D-C 这样的指定顺序进行分列。
代码如下：

SELECT gkey
FROM Greatests
ORDER BY case gkey
when 'B' then 1
when 'A' then 2
when 'D' then 3
when 'C' then 4
else null end

复制代码

免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！更多信息从访问主页：qidao123.com:ToB企服之家，中国第一个企服评测及商务社交产业平台。

欢迎光临 ToB企服应用市场:ToB评测及商务社交产业平台 (https://dis.qidao123.com/)