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

41,590 阅读5分钟

前言

在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_valuetrue的分支的结果。

第二种CASE语法返回的是第一个conditiontrue的分支的结果。

如果没有一个value=compare_value或者conditiontrue,那么就会返回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_CODESTU_NAMESTU_SEXSTU_SCORE
XM小明088
XL小磊055
XF小峰045
XH小红166
XN晓妮177
XY小伊199
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

这个例子中表结构不是很合理:姓名,性别,分数放在同一个表中;但是sumcase一起使用我还见的比较少,sum不都一般和group一起使用吗?

行转列

  • 按月份横向显示销售额
  • 按科目横向显示成绩

单纯的CASE WHEN并不能实现行转列,还需要配合SUMGROUP 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;

情况描述:表设计时将日期时间中的datehour给独立出来成两列,查询时再合并成一个新的条件;导致了这个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,原因有两点:

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

不过在做统计分析的时候,使用这类函数会感叹:真香!

参考文献