SQL之case使用

参考：SQL Case when 的使用方法

case具有两种格式：一、简单case 二、case搜索

格式1：case后跟列名，在when前

(case sex
           when '1' then '男'
           when '2' then '女'
           else '其他' 
           end) as  sex 

注释：这里表示一列：sex的数据

结果如下：

name	sex
张三	女
李四	男
王五	男
马六	女
赵七	男

格式2：列名在case和when后，仍旧是对一列的操作

(case 
     when sex = '1'  then '男'
     when sex = '2'  then '女'
     else '其他'
     end) as sex

两种方式，可以实现相同的功能。简单Case函数的写法相对比较简洁，但是和Case搜索函数相比，功能方面会有些限制，比如写判断式。
还有一个需要注意的问题，Case函数只返回第一个符合条件的值，剩下的Case部分将会被自动忽略。
--比如说，下面这段SQL，你永远无法得到“第二类”这个结果

CASE WHEN col_1 IN ( 'a', 'b') THEN '第一类'
         WHEN col_1 IN ('a')       THEN '第二类'
ELSE'其他' END

case功能

1. 已知数据按照另外一种方式进行分组，分析

国家（country）	人口（population）
中国	600
美国	100
加拿大	100
英国	200
法国	300
日本	250
德国	200
墨西哥	50
印度	250

country	tutolPopulation
北美洲	250
欧洲	700
亚洲	1100

要求：

country	tutolPopulation
北美洲	250
欧洲	700
亚洲	1100

select 
(case country
          when '中国'  then '亚洲'
          when '日本'  then '亚洲'
          when '印度'  then '亚洲'
          when '美国'  then '北美洲'
          when '加拿大'  then '北美洲'
          when '墨西哥'  then '北美洲'
          when '英国'  then '欧洲'
          when '法国'  then '欧洲'
          when '德国'  then '欧洲'
          else '其他'
          end) as country,
sum(population) as tutolPopulation
from countrylist
group by case country
          when '中国'  then '亚洲'
          when '日本'  then '亚洲'
          when '印度'  then '亚洲'
          when '美国'  then '北美洲'
          when '加拿大'  then '北美洲'
          when '墨西哥'  then '北美洲'
          when '英国'  then '欧洲'
          when '法国'  then '欧洲'
          when '德国'  then '欧洲'
          else '其他'
          end

2. 用一个SQL语句完成不同条件的分组--二维交叉表的实现
   有如下数据

国家（country）	性别（sex）	人口（population）
中国	1	340
中国	2	260
美国	1	45
美国	2	55
加拿大	1	51
加拿大	2	49
英国	1	40
英国	2	60

国家	男	女
中国	340	260
美国	45	55
加拿大	51	49
英国	40	60

按照国家和性别进行分组，得出结果如下

国家	男	女
中国	340	260
美国	45	55
加拿大	51	49
英国	40	60

普通情况下，用UNION也可以实现用一条语句进行查询。但是那样增加消耗(两个Select部分)，而且SQL语句会比较长。
下面是一个是用Case函数来完成这个功能的例子

SELECT country,
SUM( CASE WHEN sex = '1' THEN
population ELSE 0 END),  --男性人口
SUM( CASE WHEN sex = '2' THEN
population ELSE 0 END)   --女性人口
FROM  Table_A
GROUP BY country;

这样我们使用Select，完成对二维表的输出形式，充分显示了Case函数的强大。

3. 在Check中使用Case函数。

在Check中使用Case函数在很多情况下都是非常不错的解决方法。可能有很多人根本就不用Check，那么我建议你在看过下面的例子之后也尝试一下在SQL中使用Check。
下面我们来举个例子
公司A，这个公司有个规定，女职员的工资必须高于1000块。如果用Check和Case来表现的话，如下所示

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

如果单纯使用Check，如下所示

CONSTRAINT check_salary CHECK
( sex = '2' AND salary > 1000 )

女职员的条件倒是符合了，男职员就无法输入了。

4. 将sum与case结合使用，可以实现分段统计。
   如果现在希望将上表中各种性别的人数进行统计，sql语句如下：

SQL> select
   sum(case u.sex when 1 then 1 else 0 end)男性,
   sum(case u.sex when 2 then 1 else 0 end)女性,
   sum(case when u.sex <>1 and u.sex<>2 then 1 else 0 end)性别为空
 from users u;
 
        男性         女性       性别为空
---------- ---------- ----------
        3                2          0