所有的文件在SQL进阶教程 (ituring),随书下载那里
概述
文章原址
基本写法
简单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)
需求:女性员工的工资必须在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
更多推荐
SQL进阶教程—CASE表达式
发布评论