MySQL中case when用法以及注意事项

发表于

前言

在MySQL中有两个地方用到了关键字case：

在CASE Statement中不能有ELSE NULL子句，并且以END CASE结尾，而不是END。

CASE Statement主要用在复合语句中，比如存储过程；而CASE Operator则是在单条语句中用作函数。

本文介绍的主要是CASE Operator的用法。

case when的基本语法

第一种用法：

1
2
3
4
5
sql复制代码CASE value
    WHEN compare_value THEN result
    [WHEN compare_value THEN result ...]
    [ELSE result]
END

第二种用法：

1
2
3
4
5
sql复制代码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的写法。

错误的写法：

1
2
3
4
5
6
sql复制代码SELECT
    CASE (`字段`|`表达式`)
        WHEN NULL THEN '结果为假'
        ELSE '结果为真'
    END
FROM `table_name`

正确的写法为：

1
2
3
4
5
6
sql复制代码SELECT
    CASE (`字段`|`表达式`) IS NULL
        WHEN TRUE THEN '结果为真'
        ELSE '结果为假'
    END
FROM `table_name`

必须这么写的原因是：MySQL对于是否为NULL的判断不能直接用等于号=，而是用IS NULL或者IS NOT NULL。

默认值的问题

在mysql case when 的坑的这篇博客中看到这种用法，还挺有意思的。

语句1：

1
2
3
4
5
6
7
sql复制代码UPDATE categories
SET
    display_order = CASE id 
                        WHEN 1 THEN 3 
                        WHEN 2 THEN 4 
                        WHEN 3 THEN 5 
                    END;

语句2：

1
2
3
4
5
6
7
8
9
sql复制代码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。

分支返回的值类型可以不一致

1
2
3
4
5
6
sql复制代码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的使用场景

  • 根据条件转换字段含义
  • 行转列

字段转换

1
2
3
4
5
6
7
8
9
10
11
sql复制代码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 
1
2
3
4
5
6
7
sql复制代码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等子句的使用。

统计各科成绩

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
sql复制代码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;

各个部门每个月的绩效总和

1
2
3
4
5
6
7
8
9
10
11
sql复制代码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：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
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优化之后的：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
sql复制代码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：

1
2
3
4
5
6
7
8
9
10
11
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：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
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，原因有两点：

  • 可读性不高
  • 可维护性不好

不过在做统计分析的时候，使用这类函数会感叹：真香！

参考文献

本文转载自: 掘金

开发者博客 – 和开发相关的 这里全都有

0%