SQL之CASE WHEN用法详解

刺骨的言语ヽ痛彻心扉 2023-10-08 12:59 14阅读 0赞

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

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

case 
        when col_1 in ( 'a', 'b') then '第一类' 
        when col_1 in ('a')  then '第二类' 
        else'其他' end

THEN后边的值与ELSE后边的值类型应一致，否则会报错。如下：

CASE SCORE WHEN 'A' THEN '优' ELSE 0 END

'优’和0数据类型不一致则报错：

\[Err\] ORA-00932: 数据类型不一致: 应为 CHAR, 但却获得 NUMBER

##### 下面介绍几种常用场景。 #####

#### 场景1：有分数score，score<60返回不及格，score>=60返回及格，score>=80返回优秀 ####

SELECT
        STUDENT_NAME,
        (CASE WHEN score < 60 THEN '不及格'
            WHEN score >= 60 AND score < 80 THEN '及格'
            WHEN score >= 80 THEN '优秀'
            ELSE '异常' END) AS REMARK
    FROM
        TABLE

注意：如果你想判断score是否null的情况，WHEN score = null THEN ‘缺席考试’，这是一种错误的写法，正确的写法应为：

CASE WHEN score IS NULL THEN '缺席考试' ELSE '正常' END

#### 场景2：现老师要统计班中，有多少男同学，多少女同学，并统计男同学中有几人及格，女同学中有几人及格，要求用一个SQL输出结果。 ####

表结构如下：其中STU\_SEX字段，0表示男生，1表示女生。

<table> 
 <thead> 
  <tr> 
   <th>STU_CODE</th> 
   <th>STU_NAME</th> 
   <th>STU_SEX</th> 
   <th>STU_SCORE</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>XM</td> 
   <td>小明</td> 
   <td>0</td> 
   <td>88</td> 
  </tr> 
  <tr> 
   <td>XL</td> 
   <td>小磊</td> 
   <td>0</td> 
   <td>55</td> 
  </tr> 
  <tr> 
   <td>XF</td> 
   <td>小峰</td> 
   <td>0</td> 
   <td>45</td> 
  </tr> 
  <tr> 
   <td>XH</td> 
   <td>小红</td> 
   <td>1</td> 
   <td>66</td> 
  </tr> 
  <tr> 
   <td>XN</td> 
   <td>晓妮</td> 
   <td>1</td> 
   <td>77</td> 
  </tr> 
  <tr> 
   <td>XY</td> 
   <td>小伊</td> 
   <td>1</td> 
   <td>99</td> 
  </tr> 
 </tbody> 
</table>

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

输出结果如下：

<table> 
 <thead> 
  <tr> 
   <th>MALE_COUNT</th> 
   <th>FEMALE_COUNT</th> 
   <th>MALE_PASS</th> 
   <th>FEMALE_PASS</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>3</td> 
   <td>3</td> 
   <td>1</td> 
   <td>3</td> 
  </tr> 
 </tbody> 
</table>

#### 场景3：经典行转列，并配合聚合函数做统计 ####

现要求统计各个城市，总共使用了多少水耗、电耗、热耗，使用一条SQL语句输出结果

有能耗表如下：  
E\_TYPE表示能耗类型，0表示水耗，1表示电耗，2表示热耗  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NDY4ODk3Mw_size_16_color_FFFFFF_t_70]

SELECT 
    	E_CODE,
    	SUM(CASE WHEN E_TYPE = 0 THEN E_VALUE ELSE 0 END) AS WATER_ENERGY,--水耗
    	SUM(CASE WHEN E_TYPE = 1 THEN E_VALUE ELSE 0 END) AS ELE_ENERGY,--电耗
    	SUM(CASE WHEN E_TYPE = 2 THEN E_VALUE ELSE 0 END) AS HEAT_ENERGY--热耗
    FROM 
    	THTF_ENERGY_TEST
    GROUP BY
    	E_CODE

输出结果如下：  
![在这里插入图片描述][20210716170445954.png]

#### 场景4：CASE WHEN中使用子查询 ####

根据城市用电量多少，计算用电成本。假设电能耗单价分为三档，根据不同的能耗值，使用相应价格计算成本。  
价格表如下：  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NDY4ODk3Mw_size_16_color_FFFFFF_t_70 1]  
当能耗值小于10时，使用P\_LEVEL=0时的P\_PRICE的值，能耗值大于10小于30使用P\_LEVEL=1时的P\_PRICE的值…

CASE WHEN energy <= (SELECT P_LIMIT FROM TABLE_PRICE WHERE P_LEVEL = 0) THEN (SELECT P_PRICE FROM TABLE_PRICE WHERE P_LEVEL = 0)
        WHEN energy > (SELECT P_LIMIT FROM TABLE_PRICE WHERE P_LEVEL = 0) AND energy <= (SELECT P_LIMIT FROM TABLE_PRICE WHERE P_LEVEL = 1) THEN (SELECT P_PRICE FROM TABLE_PRICE WHERE P_LEVEL = 1)
        WHEN energy > (SELECT P_LIMIT FROM TABLE_PRICE WHERE P_LEVEL = 1) AND energy <= (SELECT P_LIMIT FROM TABLE_PRICE WHERE P_LEVEL = 2) THEN (SELECT P_PRICE FROM TABLE_PRICE WHERE P_LEVEL = 2)

CASE WHEN 函数使用起来简单易懂，此篇文章只作了简单的使用介绍，还需在实际工作中根据业务场景不同来灵活使用。

![在这里插入图片描述][a843d9cef40c4b718cc308543f4315f5.png]

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NDY4ODk3Mw_size_16_color_FFFFFF_t_70]: https://img-blog.csdnimg.cn/20210716170410918.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NDY4ODk3Mw==,size_16,color_FFFFFF,t_70
[20210716170445954.png]: https://img-blog.csdnimg.cn/20210716170445954.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NDY4ODk3Mw_size_16_color_FFFFFF_t_70 1]: https://img-blog.csdnimg.cn/20210716170528124.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NDY4ODk3Mw==,size_16,color_FFFFFF,t_70
[a843d9cef40c4b718cc308543f4315f5.png]: https://img-blog.csdnimg.cn/a843d9cef40c4b718cc308543f4315f5.png