SQL之CASE WHEN用法详解

SQL之CASE WHEN用法详解

引用

CSDN

1.

https://blog.csdn.net/web18285482512/article/details/145478801

CASE WHEN是SQL中一个非常重要的条件判断语句，广泛应用于数据查询和处理中。本文将详细介绍CASE WHEN的两种主要形式：简单CASE WHEN函数和CASE WHEN条件表达式函数，并通过多个实际应用场景帮助读者深入理解其使用方法。

一、简单CASE WHEN函数

简单CASE WHEN函数的语法结构如下：

CASE SCORE WHEN 'A' THEN '优' ELSE '不及格' END

这相当于使用IF函数：

IF(SCORE = 'A', '优', '不及格')

需要注意的是，THEN后边的值与ELSE后边的值类型应一致，否则会报错。例如：

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

这将导致错误：

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

简单CASE WHEN函数只能应对一些简单的业务场景，而CASE WHEN条件表达式的写法则更加灵活。

二、CASE WHEN条件表达式函数

CASE WHEN条件表达式函数类似于Java中的IF ELSE语句，其语法结构如下：

CASE 
    WHEN condition THEN result
    [WHEN...THEN...]
    ELSE result
    END

SQL语言演示：

CASE 
    WHEN SCORE = 'A' THEN '优'
    WHEN SCORE = 'B' THEN '良'
    WHEN SCORE = 'C' THEN '中' 
    ELSE '不及格' END

这等同于：

CASE score
    WHEN 'A' THEN '优'
    WHEN 'B' THEN '良'
    WHEN 'C' THEN '中'
    ELSE '不及格' END

其中，condition是一个返回布尔类型的表达式。如果表达式返回true，则整个函数返回相应result的值；如果表达式皆为false，则返回ElSE后result的值，如果省略了ELSE子句，则返回NULL。

三、常用场景

场景1：不同状态展示为不同的值

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

SELECT
    stu_name,
    (CASE WHEN stu_score < 60 THEN '不及格'
        WHEN stu_score >= 60 AND stu_score < 80 THEN '及格'
        WHEN stu_score >= 80 THEN '优秀'
        ELSE '异常' END) AS REMARK
FROM students;

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

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

场景2：统计不同状态下的值

现老师要统计班中，有多少男同学，多少女同学，并统计男同学中有几人及格，女同学中有几人及格，要求用一个SQL输出结果。其中stu_sex字段，0表示男生，1表示女生。

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
    students;

输出结果如下：

注意点：

• 使用的是sum而不是count
• THEN 1 ELSE 0的位置不能改变
• 字符 ‘0’ 和 数值 0，使用都是一样的

场景3：配合聚合函数做统计

现要求统计各个城市，总共使用了多少水耗、电耗、热耗，使用一条SQL语句输出结果。其中，E_TYPE表示能耗类型，0表示水耗，1表示电耗，2表示热耗。

select e_code,
       sum(case when e_type = 0 then e_value else 0 end) as '水耗',
       sum(case when e_type = 1 then e_value else 0 end) as '电耗',
       sum(case when e_type = 2 then e_value else 0 end) as '热耗'
from energy_test
group by e_code;

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

根据城市用电量多少，计算用电成本。假设电能耗单价分为三档，根据不同的能耗值，使用相应价格计算成本。当能耗值小于10时，使用P_LEVEL=0时的P_PRICE的值，能耗值大于10小于30使用P_LEVEL=1时的P_PRICE的值……

select e_code, e_value,
     (CASE WHEN e_value <= (SELECT p_limit FROM p_price WHERE p_level = 0)
        THEN (SELECT p_price FROM p_price WHERE p_level = 0)
     WHEN e_value > (SELECT p_limit FROM p_price WHERE p_level = 0) AND e_value <= (SELECT p_limit FROM p_price WHERE p_level = 1)
        THEN (SELECT P_PRICE FROM p_price WHERE P_LEVEL = 1)
     WHEN e_value > (SELECT p_limit FROM p_price WHERE p_level = 1) AND e_value <= (SELECT p_limit FROM p_price WHERE p_level = 2)
        THEN (SELECT p_price FROM p_price WHERE P_LEVEL = 2) end ) as price
from energy_test
where e_type = 1;

场景5：经典行转列，结合max聚合函数

行转列中 SUM作用：无用，但是select后得跟聚合函数，不能去掉sum。直接写max或者min也行。

select
    max(case when column_name = 'SHI_SHI_CODE' then comment else ''end) as SHI_SHI_CODE_COMMENT,
    max(case when column_name = 'SHUI_HAO' then comment else ''end) as SHUI_HAO_COMMENT,
    max(case when column_name = 'RE_HAO' then comment else ''end) as RE_HAO_COMMENT,
    max(case when column_name = 'YAN_HAO' then comment else ''end) as YAN_HAO_COMMENT,
    max(case when column_name = 'OTHER' then comment else '' end) as OTHER_COMMENT
from user_col_comments;