SQL 中case when ...then...else ...and用法

一时失言乱红尘 2021-12-19 17:37 631阅读 0赞

## CASE WHEN 基本概念 ##

Case具有两种格式：简单Case函数和Case搜索函数。

简单Case函数：  
CASE sex  
WHEN ‘1’ THEN ‘男’  
WHEN ‘0’ THEN ‘女’  
ELSE ‘其他’ END

Case搜索函数：  
CASE WHEN sex = ‘1’ THEN ‘男’  
WHEN sex = ‘0’ THEN ‘女’  
ELSE ‘其他’ END

显然，简单Case函数生在简洁，但是它只适用于这种单字段的单值比较，而Case搜索函数的优点在于适用于所有比较的情况。

还有一个需要注意的问题，Case函数在满足了某个符合条件后，剩下的条件将会被自动忽略，因此，即使满足多个条件，执行过程中也只认第一个条件。

在使用 CASE WHEN时，可以把它当作一个没有字段名的**字段**，字段值根据条件确认，在需要使用字段名时可以是用 as来定义别名。这么说还很抽象，看看下面 CASE WHEN的使用案例就清楚了。

## 使用场景 ##

1、可以将已知数据按照某种方式进行分组，分析。

![这里写图片描述][20161110012229519]

根据这个国家人口数据，统计亚洲和北美洲的人口数量。使用如下 SQL：

SELECT  CASE country
        WHEN '中国'     THEN '亚洲'
        WHEN '印度'     THEN '亚洲'
        WHEN '日本'     THEN '亚洲'
        WHEN '美国'     THEN '北美洲'
        WHEN '加拿大'  THEN '北美洲'
        WHEN '墨西哥'  THEN '北美洲'
        ELSE '其他' END as '洲' , SUM(population) as '人口'
    FROM test
    GROUP BY CASE country
        WHEN '中国'     THEN '亚洲'
        WHEN '印度'     THEN '亚洲'
        WHEN '日本'     THEN '亚洲'
        WHEN '美国'     THEN '北美洲'
        WHEN '加拿大'  THEN '北美洲'
        WHEN '墨西哥'  THEN '北美洲'
        ELSE '其他' END;

![这里写图片描述][20161110012410051]

这里的两个CASE WHEN都相当于一个字段，不过值得一提的是，第二个CASE WHEN 的THEN值并不用写明是什么洲，它只是用于将记录进行分组，所以THEN后面的值只有能区分这三种记录就行，GROUP BY也可以写成：

GROUP BY CASE country
        WHEN '中国'     THEN 0
        WHEN '印度'     THEN 0
        WHEN '日本'     THEN 0
        WHEN '美国'     THEN 1
        WHEN '加拿大'  THEN 1
        WHEN '墨西哥'  THEN 1
        ELSE 2 END;

2、用一个SQL语句完成不同条件的分组。

有如下数据：  
![这里写图片描述][20161110013736370]

用Case函数来完成按照国家和性别进行分组。使用如下SQL：

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;

得到如下结果：  
![这里写图片描述][20161110014154419]

就第一个CASE WHEN讲解：

CASE WHEN sex = '1' THEN
    population ELSE 0 END

当记录的sex为1时，这个字段的值为记录的population值，否则为0，因此能计算出一个国家的男性人口。

有一个SQL题在面试中出现的概率极高，最近有学生出去面试仍然会遇到这样的题目，在这里跟大家分享一下。

**题目：数据库中有一张如下所示的表，表名为sales。**

**要求：写一个SQL语句查询出如下所示的结果。**

我给出的答案是这样的：

select 年, 
    sum(case when 季度=1 then 销售量 else 0 end) as 一季度, 
    sum(case when 季度=2 then 销售量 else 0 end) as 二季度, 
    sum(case when 季度=3 then 销售量 else 0 end) as 三季度, 
    sum(case when 季度=4 then 销售量 else 0 end) as 四季度 
    from sales group by 年;

[20161110012229519]: /images/20211219/0a3a24964b8f4d0692f212d968d382fe.png
[20161110012410051]: /images/20211219/a036eb8ca6cc46c5ad18aea66bc4085e.png
[20161110013736370]: /images/20211219/35f4210348cc4867ae568476080d0214.png
[20161110014154419]: /images/20211219/c93d8754e0bc41dbb34724428b8fca12.png