前言
在MySQL中有两个地方用到了关键字case
:
在CASE Statement
中不能有ELSE NULL
子句,并且以END CASE
结尾,而不是END
。
CASE Statement
主要用在复合语句中,比如存储过程;而CASE Operator
则是在单条语句中用作函数。
本文介绍的主要是CASE Operator
的用法。
case when的基本语法
第一种用法:
CASE value
WHEN compare_value THEN result
[WHEN compare_value THEN result ...]
[ELSE result]
END
第二种用法:
CASE
WHEN condition THEN result
[WHEN condition THEN result ...]
[ELSE result]
END
两种用法的区别:
第一种CASE
语法返回的是第一个value=compare_value
为true
的分支的结果。
第二种CASE
语法返回的是第一个condition
为true
的分支的结果。
如果没有一个value=compare_value
或者condition
为true
,那么就会返回ELSE
对应的结果,如果没有ELSE
分支,那么返回NULL
。
case when的注意事项
分支之间不能有交集
这个函数是顺序执行的,每个条件之间不能有交集;倒不是MySQL的语法上不允许有交集,而是因为一旦成功匹配一条之后其他分支不会再执行了。如果没有理顺逻辑关系,查询的结果可能和预期不符。
NULL的判断
在CASE
的第一种用法中如果要判断某则字段或者表达式的是否为NULL
的写法。
错误的写法:
SELECT
CASE (`字段`|`表达式`)
WHEN NULL THEN '结果为假'
ELSE '结果为真'
END
FROM `table_name`
正确的写法为:
SELECT
CASE (`字段`|`表达式`) IS NULL
WHEN TRUE THEN '结果为真'
ELSE '结果为假'
END
FROM `table_name`
必须这么写的原因是:MySQL对于是否为NULL
的判断不能直接用等于号=
,而是用IS NULL
或者IS NOT NULL
。
默认值的问题
在mysql case when 的坑的这篇博客中看到这种用法,还挺有意思的。
语句1:
UPDATE categories
SET
display_order = CASE id
WHEN 1 THEN 3
WHEN 2 THEN 4
WHEN 3 THEN 5
END;
语句2:
UPDATE categories
SET
display_order = CASE id
WHEN 1 THEN 3
WHEN 2 THEN 4
WHEN 3 THEN 5
END
WHERE
id IN (1,2,3);
如果不用where
语句对id
进行限制,那么语句1会将id
不为1, 2, 3
的所有记录的display_order
字段都设置为NULL
。
分支返回的值类型可以不一致
SELECT
CASE
WHEN 5 % 3 = 0 THEN "情况1"
WHEN 5 % 3 = 1 THEN "情况2"
ELSE 12
END AS result;
在DataGrip
上执行这个SQL
语句没有报错,并且后面接表名查询也不报错。看到这个结果我裂开了,分支的返回类型不一致,不应该报错吗?
接着我又用JdbcTemplate
去执行了这个SQL
语句,发现居然也没有报错,ELSE
分支的值被转化为了字符串。
果然MySQL
不严谨啊!
此时,我回过头又仔细看了一眼MySQL的文档,发现其实文档上对这种情况说的十分详细。
这里简单翻译一下:
CASE
函数的返回值是所有结果值类型的聚合(aggregated type):
- 如果所有值的类型都是数值的,那么聚合类型也是数值的:
- 如果其中至少有一个值是双精度的,那么结果类型就是双精度的。
- 否则,如果至少有一个值是
DECIMAL
,那么结果的类型就是DECIMAL
。 - ......
- ......
- 对于所有其他类型的组合,结果是
VARCHAR
类型。 - 类型合并时,会忽略
NULL
值所属的类型。
中间的类型合并情况太多了,限于篇幅原因就不一一列举了,感兴趣的请移步:operate_case。
case when的使用场景
- 根据条件转换字段含义
- 行转列
字段转换
SELECT
name '姓名',
age '年龄',
CASE
WHEN age < 18 THEN '少年'
WHEN age < 30 THEN '青年'
WHEN age >= 30 AND age < 50 THEN '中年'
ELSE '老年'
END '年龄段'
FROM
user_info;
一条语句输出多个指标
有多少男同学,多少女同学,并统计男同学中有几人及格,女同学中有几人及格
表结构如下:其中STU_SEX字段,0表示男生,1表示女生。
STU_CODE | STU_NAME | STU_SEX | STU_SCORE |
---|---|---|---|
XM | 小明 | 0 | 88 |
XL | 小磊 | 0 | 55 |
XF | 小峰 | 0 | 45 |
XH | 小红 | 1 | 66 |
XN | 晓妮 | 1 | 77 |
XY | 小伊 | 1 | 99 |
SELECT
SUM (CASE WHEN STU_SEX = 0 THEN 1 ELSE 0 END) AS MALE_COUNT,
SUM (CASE WHEN STU_SEX = 1 THEN 1 ELSE 0 END) AS FEMALE_COUNT,
SUM (CASE WHEN STU_SCORE >= 60 AND STU_SEX = 0 THEN 1 ELSE 0 END) AS MALE_PASS,
SUM (CASE WHEN STU_SCORE >= 60 AND STU_SEX = 1 THEN 1 ELSE 0 END) AS FEMALE_PASS
FROM
THTF_STUDENTS
这个例子中表结构不是很合理:姓名,性别,分数放在同一个表中;但是sum
和case
一起使用我还见的比较少,sum
不都一般和group
一起使用吗?
行转列
- 按月份横向显示销售额
- 按科目横向显示成绩
单纯的CASE WHEN
并不能实现行转列,还需要配合SUM
和GROUP BY
等子句的使用。
SELECT
st.stu_id '学号',
st.stu_name '姓名',
sum(CASE co.course_name WHEN '大学语文' THEN sc.scores ELSE 0 END ) '大学语文',
sum(CASE co.course_name WHEN '新视野英语' THEN sc.scores ELSE 0 END ) '新视野英语',
sum(CASE co.course_name WHEN '离散数学' THEN sc.scores ELSE 0 END ) '离散数学',
sum(CASE co.course_name WHEN '概率论' THEN sc.scores ELSE 0 END ) '概率论',
sum(CASE co.course_name WHEN '线性代数' THEN sc.scores ELSE 0 END ) '线性代数',
sum(CASE co.course_name WHEN '高等数学' THEN sc.scores ELSE 0 END ) '高等数学'
FROM
edu_student st
LEFT JOIN edu_score sc ON st.stu_id = sc.stu_id
LEFT JOIN edu_courses co ON co.course_no = sc.course_no
GROUP BY
st.stu_id
ORDER BY
NULL;
SELECT
t1.dep,
t2.depname,
SUM(CASE mon WHEN '一月' THEN yj ELSE 0 END) AS 一月,
SUM(CASE mon WHEN '一月' THEN yj ELSE 0 END) AS 一月,
SUM(CASE mon WHEN '一月' THEN yj ELSE 0 END) AS 一月,
FROM
table_1 t1
LEFT JOIN table_2 t2 ON t1.dep = t2.dep
GROUP BY
t1.dep;
SQL优化
使用sum case when
之前的SQL
:
SELECT
(
SELECT SUM(total_fee)
FROM mall_order SS
WHERE SS.create_time = S.create_time AND SS.payment_method = 1
) AS 'zhifubaoTotalOrderAmount',
(
SELECT COUNT(*)
FROM mall_order SS
WHERE SS.create_time = S.create_time AND SS.payment_method = 1
) AS 'zhifubaoTotalOrderNum',
(
SELECT SUM(total_fee)
FROM mall_order SS
WHERE SS.create_time = S.create_time AND SS.payment_method = 2
) AS 'weixinTotalOrderAmount',
(
SELECT COUNT(*)
FROM mall_order SS
WHERE SS.create_time = S.create_time AND SS.payment_method = 2
) AS 'weixinTotalOrderNum'
FROM mall_order S
WHERE S.create_time > '2016-05-01' AND S.create_time < '2016-08-01'
GROUP BY
S.create_time
ORDER BY
S.create_time ASC;
执行情况:50w条数据,10s左右;全表扫描,4个子查询DEPENDENT SUBQUERY
,依赖于外部查询。
使用sum case when
优化之后的:
SELECT
S.create_time,
sum(case when S.payment_method =1 then 1 else 0 end) as 'zhifubaoOrderNum',
sum(case when S.payment_method =1 then total_fee else 0 end) as 'zhifubaoOrderAmount',
sum(case when S.payment_method =2 then 1 else 0 end) as 'weixinOrderNum',
sum(case when S.payment_method =2 then total_fee else 0 end) as 'weixinOrderAmount'
FROM
mall_order S
WHERE
S.create_time > '2015-05-01' and S.create_time < '2016-08-01'
GROUP BY
S.create_time
ORDER BY
S.create_time asc;
执行情况:全表扫描50w条数据,1s左右;遍历全表一次就可以得到结果了。
原来的SQL
:
SELECT
uid,
sum(power) powerup
FROM t1
WHERE
date>='2017-03-31' AND
UNIX_TIMESTAMP(STR_TO_DATE(concat(date,' ',hour),'%Y-%m-%d %H'))>=1490965200 AND
UNIX_TIMESTAMP(STR_TO_DATE(concat(date,' ',hour),'%Y-%m-%d %H'))<1492174801 AND
aType in (1,6,9)
GROUP BY
uid;
情况描述:表设计时将日期时间中的date
和hour
给独立出来成两列,查询时再合并成一个新的条件;导致了这个SQL效率非常低,全表扫描、没有索引、有临时表、需要额外排序。
优化后的SQL
:
SELECT
uid,
sum(powerup+powerup1)
FROM
(
SELECT uid,
CASE
WHEN concat(date,' ',hour) >='2017-03-24 13:00' THEN power ELSE '0'
END AS powerup,
CASE
WHEN concat(date,' ',hour) < '2017-03-25 13:00' THEN power ELSE '0'
END AS powerup1
FROM t1
WHERE date >= '2017-03-24' AND date AND aType in (1,6,9)
) a
GROUP BY
uid;
使用case when
优化之后,原来的在date
上的索引就可以用上了。
总结
个人不太喜欢在业务代码的SQL
语句中用case when
,原因有两点:
- 可读性不高
- 可维护性不好
不过在做统计分析的时候,使用这类函数会感叹:真香!
参考文献
- dev.mysql.com/doc/refman/…
- dev.mysql.com/doc/refman/…
- www.cnblogs.com/echojson/p/…
- blog.csdn.net/qq_16142851…
- blog.csdn.net/u013514928/…
- www.cnblogs.com/chenduzizho…
- www.cnblogs.com/echojson/p/…
- blog.csdn.net/qq_16142851…
- my.oschina.net/u/1187675/b…
- blog.csdn.net/weixin_3246…
- blog.csdn.net/rongtaoup/a…