CASE表达式

CASE表达式在SQL中是经常用到的，可以很方便的根据结果设置不同的返回类型，下面介绍CASE表达式和它的一些使用场景，去体验CASE表达式是如何优雅的解决一些问题的。

CASE 表达式

-- 简单CASE 表达式
CASE sex
 WHEN '1' THEN '男'
 WHEN '2' THEN '女'
ELSE '其他' END

-- 搜索CASE 表达式
CASE WHEN sex = '1' THEN '男'
     WHEN sex = '2' THEN '女'
ELSE '其他' END

注意点：
1.CASE表达式里面的数据返回类型要一致。
2.ELSE应该要填写，当没有匹配的时候CASE表达式返回的时NULL，会产生意想不到的的效果。

使用场景

1.用在SELECT 条件中

image.png

要求如果把左边表的内容转换为右边表，正常情况下想到的是
分别筛选各地男女人口，然后再用php去处理。

-- 男性人口
SELECT pref_name,
       SUM(population)
  FROM PopTbl2
 WHERE sex = '1'
 GROUP BY pref_name

-- 女性人口
SELECT pref_name,
       SUM(population)
  FROM PopTbl2
 WHERE sex = '2'
 GROUP BY pref_name

而用CASE语句在SELECT分支就可以用一条语句筛选出来

SELECT pref_name,
       -- 男性人口
       SUM( CASE WHEN sex = '1' THEN population ELSE 0 END) AS cnt_m,
       -- 女性人口
       SUM( CASE WHEN sex = '2' THEN population ELSE 0 END) AS cnt_f
  FROM  PopTbl2
 GROUP BY pref_name;

这条语句巧妙的使用了CASE语句在SELECT里面分出了结果，可以看到条件语句不一定非要写在WHERE里面。这里曾经想过为什么一定要用SUM呢，既然ElSE为0,对0和非0值进行操作岂不是一样的效果，下面这样是不是也能实现呢，实际是不能的，因为GROUP BY 聚合导致的，

SELECT pref_name,
       -- 男性人口
      ( CASE WHEN sex = '1' THEN population END) AS cnt_m,
       -- 女性人口
      ( CASE WHEN sex = '2' THEN population  END) AS cnt_f
  FROM  PopTbl2
 GROUP BY pref_name;

image.png

把group by取消发现都是两行，然而group by只能选取一行，所以要在一行展示两行的信息必须使用SUM进行汇总下，其实对于group by的语句来说，在select中出现的字段不应该出现非group by条件的字段，因为如果有多个的话，肯定只能选出一个来，这样的操作是无意义的，但是可以对未出现group by条件的字段进行聚合函数处理，对某一列出现的多行数据进行汇总计算得到一行就没有问题了。

2.用在UPDATE语句里面

image.png

假设现在需要根据以下条件对该表的数据进行更新。

对当前工资为 30 万日元以上的员工，降薪 10%。
对当前工资为 25 万日元以上且不满 28 万日元的员工，加薪 20%。

很显然如果分成多个SQL语句写会出现错误，所以可以在UPDATE语句里面用CASE条件区分开来。

   UPDATE Salaries
   SET salary = CASE WHEN salary >= 300000
                     THEN salary * 0.9
                     WHEN salary >= 250000 AND salary < 280000
                     THEN salary * 1.2
                     ELSE salary END;

3.放在对数据列的约束中

CONSTRAINT check_salary CHECK
           ( CASE WHEN sex = '2'
                  THEN CASE WHEN salary <= 200000
                            THEN 1 ELSE 0 END
                  ELSE 1 END = 1 )

上面的约束表示限制女性的工资必须小于200000，而男性没有要求，这里不能用 salary <= 20000 and sex =2 ,这样的话会增加男员工时候出错。

课后题目

1.求多列中的最大值：

image.png

SELECT `key`, CASE WHEN x >=y AND x >= z THEN x WHEN y >= x AND y >= z THEN y ELSE z END m 
from Greatests;

2.那么，请思考一个查询语句，使得结果按照 B-A-D-C 这样的指定顺序进行排列。

-- 在order by 语句中使用了case
SELECT * FROM Greatests ORDER BY 
CASE `key` WHEN 'B' THEN 0 WHEN 'A' THEN 1 WHEN 'D' THEN 2 WHEN 'C' THEN 3
ELSE -1 END DESC;