SQL之CASE WHEN用法详解_sql case when
目录
- 一、简单CASE WHEN函数：
- 二、CASE WHEN条件表达式函数
- 三、常用场景
-
- 场景1：不同状态展示为不同的值
- 场景2：统计不同状态下的值
- 场景3：配合聚合函数做统计
- 场景4：CASE WHEN中使用子查询
- 场景5：经典行转列，结合max聚合函数
一、简单CASE WHEN函数：
CASE SCORE WHEN \'A\' THEN \'优\' ELSE \'不及格\' END# 使用 IF 函数进行替换IF(SCORE = \'A\', \'优\', \'不及格\')THEN后边的值与ELSE后边的值类型应一致，否则会报错。
如下：
CASE SCORE WHEN ‘A’ THEN ‘优’ ELSE 0 END’优’和0数据类型不一致则报错：
[Err] ORA-00932: 数据类型不一致: 应为 CHAR, 但却获得 NUMBER
简单CASE WHEN函数只能应对一些简单的业务场景，而CASE WHEN条件表达式的写法则更加灵活。
二、CASE WHEN条件表达式函数
类似JAVA中的IF ELSE语句。
格式：
CASE WHEN condition THEN result [WHEN...THEN...] ELSE result ENDSQL语言演示：
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 \'不及格\' ENDcondition是一个返回布尔类型的表达式，
如果表达式返回true，则整个函数返回相应result的值，
如果表达式皆为false，则返回ElSE后result的值，如果省略了ELSE子句，则返回NULL。
三、常用场景
前言
students表的DDL
-- auto-generated definitioncreate table students( stu_code varchar(10) null, stu_name varchar(10) null, stu_sex int null, stu_score int null);students表的DML
# 其中stu_sex字段，0表示男生，1表示女生。INSERT INTO students (stu_code, stu_name, stu_sex, stu_score) VALUES (\'xm\', \'小明\', 0, 88);INSERT INTO students (stu_code, stu_name, stu_sex, stu_score) VALUES (\'xl\', \'夏磊\', 0, 55);INSERT INTO students (stu_code, stu_name, stu_sex, stu_score) VALUES (\'xf\', \'晓峰\', 0, 45);INSERT INTO students (stu_code, stu_name, stu_sex, stu_score) VALUES (\'xh\', \'小红\', 1, 89);INSERT INTO students (stu_code, stu_name, stu_sex, stu_score) VALUES (\'xn\', \'小妮\', 1, 77);INSERT INTO students (stu_code, stu_name, stu_sex, stu_score) VALUES (\'xy\', \'小一\', 1, 99);INSERT INTO students (stu_code, stu_name, stu_sex, stu_score) VALUES (\'xs\', \'小时\', 1, 45);energy_test表的DDL
-- auto-generated definitioncreate table energy_test( e_code varchar(2) null, e_value decimal(5, 2) null, e_type int null);energy_test表的DML
# 其中，E_TYPE表示能耗类型，0表示水耗，1表示电耗，2表示热耗INSERT INTO energy_test (e_code, e_value, e_type) VALUES (\'北京\', 28.50, 0);INSERT INTO energy_test (e_code, e_value, e_type) VALUES (\'北京\', 23.50, 1);INSERT INTO energy_test (e_code, e_value, e_type) VALUES (\'北京\', 28.12, 2);INSERT INTO energy_test (e_code, e_value, e_type) VALUES (\'北京\', 12.30, 0);INSERT INTO energy_test (e_code, e_value, e_type) VALUES (\'北京\', 15.46, 1);INSERT INTO energy_test (e_code, e_value, e_type) VALUES (\'上海\', 18.88, 0);INSERT INTO energy_test (e_code, e_value, e_type) VALUES (\'上海\', 16.66, 1);INSERT INTO energy_test (e_code, e_value, e_type) VALUES (\'上海\', 19.99, 0);INSERT INTO energy_test (e_code, e_value, e_type) VALUES (\'上海\', 10.05, 0);p_price表的DDL
-- auto-generated definitioncreate table p_price( p_price decimal(5, 2) null comment \'价格\', p_level int null comment \'等级\', p_limit int null comment \'阈值\') comment \'电能耗单价表\';p_price表的DML
INSERT INTO test.p_price (p_price, p_level, p_limit) VALUES (1.20, 0, 10);INSERT INTO test.p_price (p_price, p_level, p_limit) VALUES (1.70, 1, 30);INSERT INTO test.p_price (p_price, p_level, p_limit) VALUES (2.50, 2, 50);user_col_comments 表的DDL
-- auto-generated definitioncreate table user_col_comments( column_name varchar(50) null comment \'列名\', comment varchar(100) null comment \'列的备注\');user_col_comments 表的DML
INSERT INTO test.user_col_comments (column_name, comment) VALUES (\'SHI_SHI_CODE\', \'设施编号\');INSERT INTO test.user_col_comments (column_name, comment) VALUES (\'SHUI_HAO\', \'水耗\');INSERT INTO test.user_col_comments (column_name, comment) VALUES (\'RE_HAO\', \'热耗\');INSERT INTO test.user_col_comments (column_name, comment) VALUES (\'YAN_HAO\', \'盐耗\');INSERT INTO test.user_col_comments (column_name, comment) VALUES (\'OTHER\', \'其他\');场景1：不同状态展示为不同的值
有分数score，score=60返回及格，score>=80返回优秀
# 有分数score，score=60返回及格，score>=80返回优秀SELECT stu_name, (CASE WHEN stu_score = 60 AND stu_score = 80 THEN \'优秀\' ELSE \'异常\' END) AS REMARKFROM students;注意：如果你想判断score是否null的情况，WHEN score = null THEN ‘缺席考试’，这是一种错误的写法，正确的写法应为：
CASE WHEN score IS NULL THEN \'缺席考试\' ELSE \'正常\' END
场景2：统计不同状态下的值
现老师要统计班中，有多少男同学，多少女同学，并统计男同学中有几人及格，女同学中有几人及格，要求用一个SQL输出结果。其中stu_sex字段，0表示男生，1表示女生。
SELECTsum(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_PASSFROMstudents;输出结果如下：
注意点：
-
用的是 ：
sum?而不是count -
THEN 1 ELSE 0的位置不能改变：否则会有以下效果：sum(CASE WHEN stu_sex = 0 THEN \'1\' ELSE \'0\' END) AS \'男性\',改变了 sum(CASE WHEN stu_sex = 0 THEN \'0\' ELSE \'1\' END) AS \'女性\'： -
字符 ‘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_testgroup by e_code;输出结果如下：
场景4：CASE WHEN中使用子查询
根据城市用电量多少，计算用电成本。假设电能耗单价分为三档，根据不同的能耗值，使用相应价格计算成本。
当能耗值小于10时，使用P_LEVEL=0时的P_PRICE的值，能耗值大于10小于30使用P_LEVEL=1时的P_PRICE的值…
energy_test 我修改了e_type 为1的值的两条数据的e_value。
select e_code, e_value, (CASE 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) 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 pricefrom energy_testwhere 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_COMMENTfrom user_col_comments;输出结果如下：
