免费网站空间,wordpress 安全防范,漳州市城乡建设局网站6,伊宁市建设局网站本文所有案例基于《SQL进阶教程》实现。 概述 SQL中的CASE表达式是一种通用的条件表达式#xff0c;类似于其他语言中的if/else语句。它用于在SQL语句中实现条件逻辑。CASE表达式以WHEN子句开始#xff0c;后面跟着一个或多个WHEN条件#xff0c;每个WHEN条件后面跟着一个TH… 本文所有案例基于《SQL进阶教程》实现。 概述 SQL中的CASE表达式是一种通用的条件表达式类似于其他语言中的if/else语句。它用于在SQL语句中实现条件逻辑。CASE表达式以WHEN子句开始后面跟着一个或多个WHEN条件每个WHEN条件后面跟着一个THEN子句。如果任何WHEN条件为真则返回相应的THEN子句中的表达式。如果没有任何WHEN条件为真则可以选择性地使用ELSE子句来指定一个默认的表达式。
CASE表达式的语法如下
-- 简单 CASE 表达式
CASE sexWHEN 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_nameWHEN 德岛 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 PopTbl2GROUP BY pref_name;
配合check约束使用 假设某公司规定“女性员工的工资必须在 20 万日元以下”而在这个公司的人事表中这条无理的规定是使用 CHECK 约束来描述的代码如下所示。
-- 代码1
CONSTRAINT check_salary CHECK( CASE WHEN sex 2THEN CASE WHEN salary 200000THEN 1 ELSE 0 ENDELSE 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 SalariesSET salary salary * 0.9WHERE salary 300000;
-- 条件 2
UPDATE SalariesSET salary salary * 1.2WHERE salary 250000 AND salary 280000;-- 代码2
-- 用 CASE 表达式写正确的更新操作
UPDATE SalariesSET salary CASE WHEN salary 300000THEN salary * 0.9WHEN salary 250000 AND salary 280000THEN salary * 1.2ELSE 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 OpenCoursesWHERE month 200707) THEN ○ ELSE × END AS 7 月,CASE WHEN course_id IN (SELECT course_id FROM OpenCoursesWHERE month 200708) THEN ○ ELSE × END AS 8 月FROM CourseMaster;-- 表的匹配 使用 EXISTS 谓词
SELECT CM.course_name,CASE WHEN EXISTS(SELECT course_id FROM OpenCourses OCWHERE month 200706 AND OC.course_id CM.course_id) THEN ○ELSE × END AS 6 月,CASE WHEN EXISTS(SELECT course_id FROM OpenCourses OCWHERE month 200707 AND OC.course_id CM.course_id) THEN ○ELSE × END AS 7 月,CASE WHEN EXISTS(SELECT course_id FROM OpenCourses OCWHERE 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 YTHEN club_idELSE NULL END)END AS main_clubFROM StudentClubGROUP 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 1when A then 2when D then 3when C then 4else null end
