SQL进阶教程—CASE表达式

偷掉月亮的阿硕

已于 2022-07-18 19:04:42 修改

阅读量641

点赞数

分类专栏： sql进阶教程文章标签： sql 数据库

于 2022-07-14 16:46:24 首次发布

本文链接：https://blog.csdn.net/qq_58510930/article/details/125787968

版权

sql进阶教程专栏收录该内容

2 篇文章 0 订阅

订阅专栏

所有的文件在SQL进阶教程 (ituring.com.cn)，随书下载那里

概述

文章原址

基本写法

简单CASE表达式

SELECT CASE 列名称
	WHEN 匹配字符 THEN 转换字符
	WHEN 匹配字符 THEN 转换字符
ELSE 转换字符 END AS 转换的表名
FROM 表名

需求：现在我需要将1作为男生，2作为女生进行查询，其他人作为中间人，查出来表名为性别

SELECT CASE s_sex
	WHEN '1' THEN '男'
	WHEN '2' THEN '女'
ELSE '中间人' END AS 性别
FROM student

搜索CASE表达式

SELECT CASE 
	WHEN 列名称=匹配字符 THEN 转换字符
	WHEN 列名称=匹配字符 THEN 转换字符
ELSE 转换字符 END AS 转换的表名
FROM 表名

同样的需求，出来结果相同

SELECT CASE 
	WHEN s_sex='1' THEN '男'
	WHEN s_sex='2' THEN '女'
ELSE '中间人' END AS 性别
FROM student

需求：现在是找出20-26之间的年轻人

SELECT CASE 
	WHEN s_age>20 AND s_age<26 THEN '年轻人'
ELSE '不清楚' END AS 年轻否
FROM student

注意

使用case表达式的时候，要注意条件的排他性

比如执行下面的这一段sql语句

SELECT CASE 
    WHEN s_age>20 THEN '也是年轻人'
	WHEN s_age>20 AND s_age<26 THEN '年轻人'	
ELSE '不清楚' END AS 年轻否
FROM student

我们知道条件1包含条件2，那么执行结果中条件2的判断会不可达，即条件2语句不会执行

在这个过程中end不可以省略，但是else却是可以省略的，但是这样会造成不满足条件的成员返回null，如果出现错误不易追查，所以一般写上else语句

统计数据

转化已有的数据

需求：对于这个表格，我们要统计各个地区的人口数

#我们假设东京是地区1，其他地区为地区2，我们首先要清楚这个是按照地区名称进行分组的，这样才会对不同地区的人进行统计
SELECT CASE
	WHEN pref_name='东京' THEN '地区一'
	WHEN pref_name!='东京' THEN '地区二'
	ELSE '其他地区' END AS 地区名,SUM(population)
	FROM `poptbl`
	GROUP BY 地区名

需求：现在需要查询每一个县对应级别的数量

#假设100以下为第一级别，100-200为第二级别，200-300为三级别，300以上为四级别
SELECT CASE
           WHEN population < 100 THEN '01'
           WHEN population >= 100 AND population < 200 THEN '02'
           WHEN population >= 200 AND population < 300 THEN '03'
           WHEN population >= 300 then '04'
else '不清楚' end as 级别编号,count(*) as 数量
from poptbl
group by 级别编号
order by 级别编号

注意：我们在这里引用的使用的group by+别名等等别名信息是违反了sql的规则的，但是group by语句的执行顺序先于select语句，在oracle，sql server中这样使用会出现错误，但是在mysql中会首先进行扫描，所以sql会识别出来。

sql语句执行顺序：1、最先执行from tab；2、where语句是对条件加以限定；3、分组语句【group by…… having】；4、聚合函数；5、select语句；6、order by排序语句。

正规的写法：

#假设100以下为第一级别，100-200为第二级别，200-300为三级别，300以上为四级别
SELECT CASE
           WHEN population < 100 THEN '01'
           WHEN population >= 100 AND population < 200 THEN '02'
           WHEN population >= 200 AND population < 300 THEN '03'
           WHEN population >= 300 THEN '04'
           ELSE '不清楚' END AS 级别编号,
       COUNT(*)           AS 数量
FROM poptbl
GROUP BY CASE
             WHEN population < 100 THEN '01'
             WHEN population >= 100 AND population < 200 THEN '02'
             WHEN population >= 200 AND population < 300 THEN '03'
             WHEN population >= 300 THEN '04'
             ELSE '不清楚' END 
ORDER BY CASE
    WHEN population < 100 THEN '01'
    WHEN population >= 100 AND population < 200 THEN '02'
    WHEN population >= 200 AND population < 300 THEN '03'
    WHEN population >= 300 THEN '04'
    ELSE '不清楚' END

不同条件统计

需求：按照性别，县名称汇总

# 对于普通的查询语句，只能查询出来一个单列的结果，如果想要在下面的列旁边在加上一个女列，那么我们需要添加sex='2'和SUM(population) AS '女',但是这样的语句执行会发生错误，而且统计不准确
SELECT pref_name,SUM(population) AS '男'
FROM poptbl2
WHERE sex='1'
GROUP BY pref_name

# CASE WHEN sex='1' THEN population ELSE 0 END表示当sex=1的时候，取出population，否则为0
SELECT pref_name,
       SUM(CASE WHEN sex='1' THEN population ELSE 0 END ) AS '男',
       SUM(CASE WHEN sex='2' THEN population ELSE 0 END ) AS '女'
       FROM poptbl2
GROUP BY pref_name

使用CHECK约束定义

SQL CHECK 约束 | 菜鸟教程 (runoob.com)

需求：女性员工的工资必须在20万日元以下

#这个语句一般建立报个的情况下使用
CREATE TABLE Persons
(
sex CHAR,
salary INT,
CONSTRAINT check_salary
CHECK  (CASE WHEN sex='2' THEN CASE WHEN salary<20000 THEN 1 ELSE 0 END ELSE 1 END=1)
)

我们分开解释上面的语句，中间语句CASE WHEN salary<20000 THEN 1 ELSE 0 END，表示当小于20000的时候为1，其他情况为0，外围CASE WHEN sex=‘2’ +中间语句+ END ELSE 1 END，当sex=2的时候，执行中间语句，结果为中间语句的结果，而最终的=1，判断是否与1相等返回boolean值，为1表示符合条件

在此时我们仍旧可以存储女员工，男员工，只不过对女员工做了限制，但是如果现在我们更改掉这个约束，改为

#撤销约束
ALTER TABLE persons
	DROP CHECK check_salary
	
#创建新的约束
ALTER TABLE persons
	ADD CHECK (CASE WHEN sex='2' AND salary<200000 THEN 1 ELSE 0 END =1)

也就是条件变为and的关系，那么现在我们就无法插入男性了，因为必须满足这两个条件

在update语句进行条件分支

需求：对30万以上的人员降薪10%，对25-30的人员加薪20%

我们首先进行普通的语句

UPDATE salaries
SET salary=salary * 0.9 WHERE salary>=300000

UPDATE salaries SET salary=salary*1.2 WHERE salary>250000 AND salary<300000;

进行完成之后我们会发现这样应该会满足要求，但是我们看最终的结果，相田君怎么还涨工资了？？？？在我们正常的开发流程中，这应该是两个两个sql语句，但是两个sql语句是有执行顺序的呀

那么现在我们需要的是对这些人同时进行更改

#注意这里的else一定要写，不写的话默认为null，null加入运算结果为null
UPDATE salaries
SET salary=salary * CASE 
    WHEN salary >= 300000 THEN 0.9
    WHEN salary> 250000 AND salary<300000 THEN 1.2
    ELSE 1 END

注意：我们仔细观察下来两个方式的区别

# 搜索case方法
SELECT CASE 
    WHEN salary >= 300000 THEN 0.9
    WHEN salary> 250000 AND salary<300000 THEN 1.2
    ELSE 1 END FROM salaries
#在这里不小心添加一个salary，但是结果却始终为1，这是因为在这个判断中salary与when之后的进行匹配判断，但是都不符合，所以全部为1
 SELECT CASE salary
    WHEN salary >= 300000 THEN 0.9
    WHEN salary> 250000 AND salary<300000 THEN 1.2
    ELSE 1 END FROM salaries

需求：对于下面的数据，在主键值为a的时候，我们需要将a调换为b，当为b的时候，将b调换为a

#普通的做法，当为a的时候，我们设置一个无关值，带存储a
UPDATE SomeTable SET p_key='z' WHERE p_key='a';
UPDATE SomeTable SET p_key='a' WHERE p_key='b';
UPDATE SomeTable SET p_key='b' WHERE p_key='z';

当然此时我们使用case表达式可以轻松转换这个

#在mysql中执行这一条语句会发生错误，但是在oracel，sqlserver中执行是没有问题的，主键的检测应该发生在更新完成之后，中间出现重复不会影响，只不过mysql的检测方式,当然我们可以使用这个语句对mysql的非主键进行更改
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');

表之间的数据匹配

case表达式中可以使用判断表达式，比如between，in，<等等符号

需求：现在有两张这样的表格，我们需要生成交叉表，便于了解每一个月开设的课程

SELECT course_name,
	CASE WHEN course_id IN (SELECT course_id FROM opencourses WHERE monthNum=200706) THEN '√' ELSE '×' END AS '六月',
	CASE WHEN course_id IN (SELECT course_id FROM opencourses WHERE monthNum=200707) THEN '√' ELSE '×' END AS '七月',
	CASE WHEN course_id IN (SELECT course_id FROM opencourses WHERE monthNum=200708) THEN '√' ELSE '×' END AS '八月'
	FROM coursemaster
	#我们来分析以下为什么这么写，首先结果中显示的course_name为行，所以要对coursemaster进行查询，select ？ from coursemaster
	#其次我们想要多出来六月列，七月列等，需要进行匹配，而匹配之后得到列命名为月份，case  when ? then ? else ? end as '月份'
	#最终筛选出六月份的的课程，先查询出六月份的开设课程的id    SELECT course_id FROM opencourses WHERE monthNum=200706
	#判断这个课程是否包含在里面  in （6月份开设的课程）

现在使用exists进行练习

SELECT cM.course_name,
	CASE WHEN EXISTS (SELECT course_id FROM  opencourses oP WHERE monthNum=200706 AND oP.course_id=cM.`course_id`) THEN '√' ELSE '×' END AS '六月',
	CASE WHEN EXISTS (SELECT course_id FROM  opencourses oP WHERE monthNum=200707 AND oP.course_id=cM.`course_id`) THEN '√' ELSE '×' END AS '七月',
	CASE WHEN EXISTS (SELECT course_id FROM  opencourses oP WHERE monthNum=200708 AND oP.course_id=cM.`course_id`) THEN '√' ELSE '×' END AS '八月'
	FROM CourseMaster cM;

下面是sql进阶教程的一段原话，目前还没看懂（插眼）

无论使用 IN 还是 EXISTS，得到的结果是一样的，但从性能方面来说， EXISTS 更好。通过 EXISTS 进行的子查询能够用到“month, course_ id”这样的主键索引，因此尤其是当表 OpenCourses 里数据比较多的时候更有优势。

表达式中使用聚合函数

需求1：获取只加入一个社团的学生的社团id

需求2：获取加入多个社团的学生的主社团id

对于加入了多个社团的学生，我们通过将其“主社团标志”列设置为 Y 或者 N 来表明哪一个社团是他的主社团；对于只加入了一个社团的学生，我们将其“主社团标志”列设置为 N。

#对于第一个需求，我们只需要筛选出在std_id的中只出现一次的，即为满足条件的
SELECT * FROM StudentClub GROUP BY std_id HAVING COUNT(std_id)=1
#对于第二个，我们需要找到在其中出现多次的id，并且main_club为Y,但是遗憾的是这个是错误的，执行顺序为首先判断main_club_flg='Y'，where已经提前筛选完毕了，此时只剩下两个，count之后其实都是1，
SELECT std_id ,club_id FROM StudentClub WHERE main_club_flg='Y' GROUP BY std_id HAVING COUNT(std_id)>1 
#其实只需要执行下面发语句就够了，凸(艹皿艹 )
SELECT std_id ,club_id FROM StudentClub WHERE main_club_flg='Y'

现在使用case表达式，一次性挑选出来每一个人发主社团id

我们先来展示一下一般思路，首先以std_id分组，GROUP BY std_id，分组之后匹配count（*），如果是1表明只加入一个社团，那么这个社团就是主社团，那么其他的说明是多个社团，所以挑选出WHEN main_club_flg=‘Y’，就是主社团,所以我们执行以下语句

SELECT std_id,
	CASE  WHEN COUNT(std_id)=1 THEN club_id
	ELSE (CASE WHEN main_club_flg='Y' THEN club_id ELSE NULL END)
	END AS  main_club
	FROM StudentClub
	GROUP BY std_i

但是我们会发现这个200的学生主社团为null？？？

我们单独执行以下CASE WHEN main_club_flg=‘Y’ THEN club_id ELSE NULL END这个语句

SELECT std_id,CASE WHEN  main_club_flg='Y' THEN club_id ELSE NULL END FROM StudentClub

在按照分组执行一下，发现200居然为null，在这个的执行过程中，虽然200在遇到Y的时候，确实值为Y,但是我们没有明确告知返回哪一个值，即返回的200的值在null，3，null按照默认的顺序返回第一个，而100之所以可以正确返回，是因为他的主社团就在第一位，如果顺序一变，那么100学生也会返回错误的值

SELECT std_id,CASE WHEN  main_club_flg='Y' THEN club_id ELSE NULL END FROM StudentClub GROUP BY std_id

所以为了返回正确的值，我们需要为他们指出明确的返回条件，在null，3，null中返回3.在1，null中返回1，那么使用max进行返回

max（列表），而列表中存储的是分组得到的结果，所以正确的为

SELECT std_id,
	CASE  WHEN COUNT(std_id)=1 THEN 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 WHEN COUNT(std_id)=1 THEN club_id不需要添加，应为只有一行，不需要担心这个问题。

练习

练习1

SELECT key_word,
CASE WHEN X>Y THEN 
(CASE WHEN X>Z THEN X ELSE Z END )
 ELSE 
(CASE WHEN Z>Y THEN Z ELSE Y END)
 END AS MAX_NUM
FROM test GROUP BY key_word

使用另外一种方法

SELECT key_word,X AS col FROM test
UNION ALL
SELECT key_word,Y AS col FROM test
UNION ALL
SELECT key_word,Z AS col FROM test

SELECT key_word,MAX(col) AS max_num FROM(
SELECT key_word,X AS col FROM test
UNION ALL
SELECT key_word,Y AS col FROM test
UNION ALL
SELECT key_word,Z AS col FROM test) temp

GROUP BY key_word

或者说使用提供的函数

SELECT key_word,GREATEST(GREATEST(X,Y),Z) AS MAX_num FROM test

练习2

其实这个可以分为两部分，首先，我们可以确定得到这个是按照sex进行分组的，并且1的时候为男，2的时候为女，那么现在

SELECT 
CASE WHEN sex='1' THEN '男' ELSE '女' END AS '性别',
SUM(population) AS '全国'
FROM poptbl2
GROUP BY sex;

接下来就是将德岛那些进行插入了

SELECT 
CASE WHEN sex='1' THEN '男' ELSE '女' END AS '性别',
SUM(population) AS '全国',
(CASE WHEN pref_name='德岛' THEN population ELSE  0 END) AS '德岛'
FROM poptbl2
GROUP BY sex;

这里德岛尽然返回0，还是前面的那个问题，如果如果我们不指定这个返回的列，就会默认返回第一列，第一列是东京的，东京不匹配，所以返回0，所以我们使用max就ok

SELECT 
CASE WHEN sex='1' THEN '男' ELSE '女' END AS '性别',
SUM(population) AS '全国',
MAX(CASE WHEN pref_name='德岛' THEN population ELSE  0 END) AS '德岛',
MAX(CASE WHEN pref_name='香川' THEN population ELSE  0 END) AS '香川',
MAX(CASE WHEN pref_name='爱媛' THEN population ELSE  0 END) AS '爱媛',
MAX(CASE WHEN pref_name='高知' THEN population ELSE  0 END) AS '高知'
FROM poptbl2
GROUP BY sex;

到这里就仅仅差最终的一步了，就是将前面的和起来，称为i四国加在一起

首先我们需要将这个分类

CASE 
 WHEN pref_name='德岛' THEN '四国' 
 WHEN pref_name='香川' THEN '四国' 
 WHEN pref_name='爱媛' THEN '四国'
 WHEN pref_name='高知' THEN '四国'
 ELSE pref_name END
 #当然也有另外一个写法
 case when pref_name in('德岛','香川','爱媛',','高知') then population else 0 end

此时要将这个分类的结果==‘四国’ 的时候进行保留，然后其他的为0，进行sum求和

SUM(CASE WHEN 
(CASE 
 WHEN pref_name='德岛' THEN '四国' 
 WHEN pref_name='香川' THEN '四国' 
 WHEN pref_name='爱媛' THEN '四国'
 WHEN pref_name='高知' THEN '四国'
 ELSE pref_name END)='四国' THEN population ELSE  0 END) AS '四国'

那么总的来说

SELECT 
CASE WHEN sex='1' THEN '男' ELSE '女' END AS '性别',
SUM(population) AS '全国',
MAX(CASE WHEN pref_name='德岛' THEN population ELSE  0 END) AS '德岛',
MAX(CASE WHEN pref_name='香川' THEN population ELSE  0 END) AS '香川',
MAX(CASE WHEN pref_name='爱媛' THEN population ELSE  0 END) AS '爱媛',
MAX(CASE WHEN pref_name='高知' THEN population ELSE  0 END) AS '高知',
SUM(CASE WHEN 
(CASE 
 WHEN pref_name='德岛' THEN '四国' 
 WHEN pref_name='香川' THEN '四国' 
 WHEN pref_name='爱媛' THEN '四国'
 WHEN pref_name='高知' THEN '四国'
 ELSE pref_name END)='四国' THEN population ELSE  0 END) AS '四国'
FROM poptbl2
GROUP BY sex

练习3

SELECT key_word,CASE key_word
		WHEN 'B' THEN 1
		WHEN 'A' THEN 2
		WHEN 'D' THEN 3
		WHEN 'C' THEN 4
		ELSE NULL END AS SORT FROM (SELECT key_word,GREATEST(GREATEST(X,Y),Z) AS MAX_num FROM test) temp
		ORDER BY SORT

偷掉月亮的阿硕

关注

0
点赞
踩
1

收藏

觉得还不错? 一键收藏
0
评论
SQL进阶教程—CASE表达式

所有的文件在SQL进阶教程 (ituring.com.cn)，随书下载那里需求：现在我需要将1作为男生，2作为女生进行查询，其他人作为中间人，查出来表名为性别同样的需求，出来结果相同需求：现在是找出20-26之间的年轻人注意使用case表达式的时候，要注意条件的排他性比如执行下面的这一段sql语句我们知道条件1包含条件2，那么执行结果中条件2的判断会不可达，即条件2语句不会执行需求：对于这个表格，我们要统计各个地区的人口数需求：现在需要查询每一个县对应级别的数量注意：我们在这里引用的
复制链接

扫一扫

专栏目录