SQL进阶之CASE表达式的使用

CASE表达式概述

CASE表达式是从SQL-92标准开始被引入的。相对于Oracle中的DECODE函数和MySQL中的IF函数，CASE表达式不依赖于具体的数据库技术，可以提高SQL代码的可移植性，所以强烈建议大家学会使用CASE表达式。

CASE表达式的两种写法

• 简单CASE表达式

    CASE 判断条件（字段名）
      WHEN  值1 THEN 返回值1
      WHEN  值2 THEN 返回值2
    ELSE ’其他’ END

• 搜索CASE表达式

    CASE WHEN 判断条件（字段名）= 值1 THEN 返回值1
         WHEN 判断条件（字段名）= 值2 THEN 返回值2
    ELSE ’其他’ END

这两种写法的执行结果是相同的。简单CASE表达式写法相对更简单一些，但是能实现的事情比较有限，所以实际开发中，推荐使用搜索CASE表达式。

使用CASE表达式的注意事项

• 使用WHEN子句时要注意条件的排他性

--在发现条件为真的WHEN字句时，CASE表达式的真假判断就中止了，例如下面代码的执行结果中不会出现“2”
  CASE WHEN col_1 IN ('a', 'b') THEN ’1’
       WHEN col_1 IN ('a')       THEN ’2’
  ELSE ’0’ END

• 不要忘记写END
• 养成写ELSE字句的习惯：不写END会报错，但是ELSE是可选的，不写也不会出错，但是不写ELSE字句的情况下，CASE表达式返回的结果是NULL，可能会造成“语法没有错误，结果却不对”这种不易追查原因的麻烦，所以，强烈建议养成写ELSE字句的习惯

CASE表达式的应用

将已有编号方式转换成新的方式并统计

我们经常会遇到将已有编号方式转换为另外一种便于分析的方式并进行统计的需求，例如：根据表中记录的市（区）名统计出所属各个省的人口数。

• 数据源表city
  
• 统计结果
• 代码实现

    SELECT  CASE
                    WHEN city.`cname`='海淀' THEN '北京'
                    WHEN city.`cname`='朝阳' THEN '北京'
                    WHEN city.`cname`='通州' THEN '北京'

                    WHEN city.`cname`='石家庄' THEN '河北'
                    WHEN city.`cname`='保定' THEN '河北'
                    WHEN city.`cname`='唐山' THEN '河北'

                    WHEN city.`cname`='青岛' THEN '山东'
                    WHEN city.`cname`='济南' THEN '山东'
            ELSE '其他' END AS province,
            SUM(population)
      FROM  city
      GROUP BY CASE
                    WHEN city.`cname`='海淀' THEN '北京'
                    WHEN city.`cname`='朝阳' THEN '北京'
                    WHEN city.`cname`='通州' THEN '北京'

                    WHEN city.`cname`='石家庄' THEN '河北'
                    WHEN city.`cname`='保定' THEN '河北'
                    WHEN city.`cname`='唐山' THEN '河北'

                    WHEN city.`cname`='青岛' THEN '山东'
                    WHEN city.`cname`='济南' THEN '山东'
            ELSE '其他' END
/*
需要注意的点：
为什么GROUP BY字句后不使用列的别名province？
	这种写法虽然更简洁，但却是违反SQL规则的。因为GROUP BY比SELECT语句先执行，所以在GROUP BY字
	句中引用SELECT字句里定义的别名是不被允许的。不过也有支持这种SQL语句的数据库，比如PostgreSQL、
	MySQL,这是因为，这些数据库在执行查询语句时，会先对SELECT子句里的列表进行扫描，并对列进行计算。
	但是在Oracle、SQL Server等数据库中坚决不可以采用这种写法。
*/