发布时间：2025-06-24 17:33:13  作者：北方职教升学中心  阅读量：494

常用场景

前言

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表示女生。常用场景
场景1：不同状态展示为不同的值
场景2：统计不同状态下的值
场景3：配合聚合函数做统计
场景4：CASE WHEN中使用子查询
场景5：经典行转列，结合max聚合函数
一、直接写max或者min也行。
如下：
CASE SCORE WHEN ‘A’ THEN ‘优’ ELSE 0 END’优’和0数据类型不一致则报错：
[Err] ORA-00932: 数据类型不一致: 应为 CHAR, 但却获得 NUMBER
简单CASE WHEN函数只能应对一些简单的业务场景，而CASE WHEN条件表达式的写法则更加灵活。CASE WHEN条件表达式函数
类似JAVA中的IF ELSE语句。
三、电耗、
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_PASSFROM	students;
输出结果如下：
注意点：
用的是 ：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：配合聚合函数做统计
现要求统计各个城市，总共使用了多少水耗、CASE WHEN条件表达式函数
三、
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 pricefrom energy_testwhere e_type = 1;
输出结果如下：
场景5：经典行转列，结合max聚合函数
行转列中 SUM作用：无用，但是select后得跟聚合函数，不能去掉sum。
二、简单CASE WHEN函数：
二、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>=60返回及格，score>=80返回优秀

# 有分数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 REMARKFROM students;

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

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

现老师要统计班中，有多少男同学，多少女同学，并统计男同学中有几人及格，女同学中有几人及格，要求用一个SQL输出结果。
当能耗值小于10时，使用P_LEVEL=0时的P_PRICE的值，能耗值大于10小于30使用P_LEVEL=1时的P_PRICE的值…

energy_test 我修改了e_type 为1的值的两条数据的e_value。

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;

输出结果如下：