sql case when用法

文章目录

学习链接

SQL之CASE WHEN用法详解

CASE When的用法

CASE WHEN函数语句多条件下使用详解

case when还可以与聚合函数配合group by一起使用，达到对同一组内的数据分别统计的效果

SQL之CASE WHEN用法详解

简单CASE WHEN函数

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

等同于，使用CASE WHEN条件表达式函数实现：

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

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

CASE SCORE WHEN'A'THEN'优'ELSE0END

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

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

CASE WHEN条件表达式函数

简单CASE WHEN函数只能应对一些简单的业务场景，而CASE WHEN条件表达式的写法则更加灵活。
CASE WHEN条件表达式函数：类似JAVA中的IF ELSE语句。

格式：

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

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

常用场景

场景1：简单条件使用

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

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

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

CASEWHEN score ISNULLTHEN'缺席考试'ELSE'正常'END

场景2：多目标字段统计

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

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

SELECTSUM(CASEWHEN STU_SEX =0THEN1ELSE0END)AS MALE_COUNT,-- 将整个表当作1个大的分组，以此类推，可以结合group by一起使用, 对分组后的每个分组这样去作聚合SUM(CASEWHEN STU_SEX =1THEN1ELSE0END)AS FEMALE_COUNT,SUM(CASEWHEN STU_SCORE >=60AND STU_SEX =0THEN1ELSE0END)AS MALE_PASS,SUM(CASEWHEN STU_SCORE >=60AND STU_SEX =1THEN1ELSE0END)AS FEMALE_PASS
FROM 
    THTF_STUDENTS

输出结果如下：

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

场景3：经典行转列，并配合聚合函数做统计
现要求统计各个城市，总共使用了多少水耗、电耗、热耗，使用一条SQL语句输出结果

有能耗表如下：其中，E_TYPE表示能耗类型，0表示水耗，1表示电耗，2表示热耗

SELECT 
    E_CODE,SUM(CASEWHEN E_TYPE =0THEN E_VALUE ELSE0END)AS WATER_ENERGY,--水耗SUM(CASEWHEN E_TYPE =1THEN E_VALUE ELSE0END)AS ELE_ENERGY,--电耗SUM(CASEWHEN E_TYPE =2THEN E_VALUE ELSE0END)AS HEAT_ENERGY--热耗FROM 
    THTF_ENERGY_TEST
GROUPBY
    E_CODE

输出结果如下：

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

场景4：CASE WHEN中使用子查询
根据城市用电量多少，计算用电成本。假设电能耗单价分为三档，根据不同的能耗值，使用相应价格计算成本。

价格表如下：

当能耗值小于10时，使用P_LEVEL=0时的P_PRICE的值，能耗值大于10小于30使用P_LEVEL=1时的P_PRICE的值…

CASEWHEN 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)

场景5：结合max聚合函数

CASE WHEN函数语句多条件下使用详解

CASE WHEN函数语句，实现简单CASE函数和CASE搜索函数两种格式。
同时配合 SUM以及COUNT方法的使用

简单CASE函数

CASE 条件参数名称
    WHEN 参数值1THEN'显示值1'WHEN 参数值2THEN'显示值2'...ELSE'显示其他值'END

如：

SELECT
    orderId,CASE state
        WHEN1THEN'启动'WHEN2THEN'关闭'ELSE'未知状态'ENDAS statusName
FROM t_table

CASE搜索函数

**CASE搜索函数**CASEWHEN 条件参数名称 ='参数值1'THEN'显示值1'WHEN 条件参数名称 ='参数值2'THEN'显示值2'...ELSE'显示其他值'END

如：

SELECT
    orderId,CASEWHEN state ='1'THEN'启动'WHEN state ='2'THEN'关闭'ELSE'未知状态'ENDAS stateName
FROM t_table

这两种格式，可以实现相同的功能。但是简单CASE函数和CASE搜索函数相比，功能方面会有些限制；

判断式

SELECT
    orderId,CASEWHEN state ='1'THEN'启动'WHEN state ='2'THEN'关闭'WHEN state IN('3','4')THEN'待解锁'ELSE'未知状态'ENDAS stateName
FROM t_table

若是多重时，WHEN 的 IN 条件中的值和下一个 WHEN 的条件重合会被忽略，如下面的 “关闭” 状态会被忽略，永远无法得到 “关闭”

SELECT
    orderId,CASEWHEN state ='1'THEN'启动'WHEN state IN('2','3','4')THEN'待解锁'WHEN state ='2'THEN'关闭'ELSE'未知状态'ENDAS stateName
FROM t_table

同时配合 SUM 以及 COUNT 方法的使用

① SUM函数

**简单CASE函数**SUM(CASE 条件参数名称
        WHEN 参数值 THEN'显示被求和值'ELSE0END)AS SUMAMT
 
 
**CASE搜索函数**SUM(CASEWHEN 条件参数名称 ='参数值'THEN'显示被求和值'ELSE0END)AS SUMAMT
 
 
**或者**SUM(CASEWHEN 
            条件参数名称1='参数值1'AND 条件参数名称2='参数值2'...THEN'显示被求和值'ELSE0END)AS SUMAMT
    
 
**或者**SUM(CASEWHEN 
            条件参数名称1IN('参数值n')AND 条件参数名称2='参数值2'...THEN'显示被求和值'ELSE0END)AS SUMAMT

示例： 根据时间条件查询值，有值时显示 “被求和值”， 没有值时，显示 “null”
状态：state
类型：t_type
金额：amt

**简单CASE函数**SELECTSUM(CASE state
        WHEN1THEN amt
    ELSE0END)AS sumAmt
FROM 
    t_table
WHERE
    core_time >='2020-07-01 00:00:00'AND core_time <='2020-07-20 23:59:59'GROUPBY core_time
 
 
**CASE搜索函数**SELECTSUM(CASEWHEN state ='1'THEN amt
    ELSE0END)AS sumAmt
FROM 
    t_table
WHERE
    core_time >='2020-07-01 00:00:00'AND core_time <='2020-07-20 23:59:59'GROUPBY core_time
 
 
**或者**SELECTSUM(CASEWHEN state ='1'AND t_type ='2'THEN amt
    ELSE0END)AS sumAmt
FROM 
    t_table
WHERE
    core_time >='2020-07-01 00:00:00'AND core_time <='2020-07-20 23:59:59'GROUPBY core_time
 
 
**或者**SELECTSUM(CASEWHEN state IN('2','3','4')AND t_type ='2'THEN amt
    ELSE0END)AS sumAmt
FROM 
    t_table
WHERE
    core_time >='2020-07-01 00:00:00'AND core_time <='2020-07-20 23:59:59'GROUPBY core_time

② COUNT函数

**简单CASE函数**COUNT(CASE 条件参数名称
        WHEN 参数值 THEN1ELSE0END)AS COUNTAMT
 
 
**CASE搜索函数**COUNT(CASEWHEN 条件参数名称 ='参数值'THEN1ELSE0END)AS COUNTAMT
 
 
**或者**COUNT(CASEWHEN 
            条件参数名称1='参数值1'AND 条件参数名称2='参数值2'...THEN1ELSE0END)AS COUNTAMT
 
    
**或者**COUNT(CASEWHEN 
            条件参数名称1IN('参数值n')AND 条件参数名称2='参数值2'...THEN1ELSE0END)AS COUNTAMT

示例： 根据时间条件查询值，有值时显示 “总条数值”， 没有值时，显示 “0”
状态：state
类型：t_type
金额：amt

**简单CASE函数**SELECTCOUNT(CASE state
        WHEN1THEN1ELSE0END)AS countAmt
FROM 
    t_table
WHERE
    core_time >='2020-07-01 00:00:00'AND core_time <='2020-07-20 23:59:59'GROUPBY core_time
 
 
**CASE搜索函数**SELECTCOUNT(CASEWHEN state ='1'THEN1ELSE0END)AS countAmt
FROM 
    t_table
WHERE
    core_time >='2020-07-01 00:00:00'AND core_time <='2020-07-20 23:59:59'GROUPBY core_time
 
 
**或者**SELECTCOUNT(CASEWHEN state ='1'AND t_type ='2'THEN1ELSE0END)AS countAmt
FROM 
    t_table
WHERE
    core_time >='2020-07-01 00:00:00'AND core_time <='2020-07-20 23:59:59'GROUPBY core_time
 
 
**或者**SELECTCOUNT(CASEWHEN state IN('2','3','4')AND t_type ='2'THEN1ELSE0END)AS countAmt
FROM 
    t_table
WHERE
    core_time >='2020-07-01 00:00:00'AND core_time <='2020-07-20 23:59:59'GROUPBY core_time