目录
原文链接:https://blog.csdn.net/rongtaoup/article/details/82183743
前言
建议先看看官方文档_对 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’优’和0数据类型不一致则报错:
[Err] ORA-00932: 数据类型不一致: 应为 CHAR, 但却获得 NUMBER
简单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。
三、常用场景
前言
students表的DDL
-- auto-generated definition
create 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 definition
create 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 definition
create 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 definition
create 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 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
的位置不能改变:否则会有以下效果: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_test
group 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)
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;
输出结果如下:
四、练习题
LeetCode练习题
练习题一
题目描述
create table test
(
year int null,
mouth int null,
amout decimal(2, 1) null
);
INSERT INTO test.test (year, mouth, amout) VALUES (1991, 1, 1.1);
INSERT INTO test.test (year, mouth, amout) VALUES (1991, 2, 1.2);
INSERT INTO test.test (year, mouth, amout) VALUES (1991, 3, 1.3);
INSERT INTO test.test (year, mouth, amout) VALUES (1991, 4, 1.4);
INSERT INTO test.test (year, mouth, amout) VALUES (1992, 1, 2.1);
INSERT INTO test.test (year, mouth, amout) VALUES (1992, 2, 2.2);
INSERT INTO test.test (year, mouth, amout) VALUES (1992, 3, 2.3);
INSERT INTO test.test (year, mouth, amout) VALUES (1992, 4, 2.4);
全部数据如下:
要求,最后用SQL语句转化为如下结果:
SQL 实现
select t.year,
sum(case when t.mouth = 1 then amout else 0 end ) as m1,
sum(case when t.mouth = 2 then amout else 0 end ) as m2,
sum(case when t.mouth = 3 then amout else 0 end ) as m3,
sum(case when t.mouth = 4 then amout else 0 end ) as m4
from test t
group by t.year
思路
行转列第一个就是想到了case when
语法。
那么,看题目,以年份year
作为筛选条件,去找每个月份mouth
对应的金额amout
。根据题目的意思,如果月份mouth 是1 ,那么找出来的金额amout 就是 m1。
此时就有了初步的轮廓:
select t.year,
(case when t.mouth = 1 then amout else 0 end ) as m1,
(case when t.mouth = 2 then amout else 0 end ) as m2,
(case when t.mouth = 3 then amout else 0 end ) as m3,
(case when t.mouth = 4 then amout else 0 end ) as m4
from test t
此时,得到的结果是:
得到这一步就很好理解了,我们就需要一个统计函数,就可以得到想要的结果了。
select t.year,
sum(case when t.mouth = 1 then amout else 0 end ) as m1,
sum(case when t.mouth = 2 then amout else 0 end ) as m2,
sum(case when t.mouth = 3 then amout else 0 end ) as m3,
sum(case when t.mouth = 4 then amout else 0 end ) as m4
from test t
group by t.year
完美解决:
练习题二
题目描述
-- auto-generated definition
create table plant_plan_rule_info
(
rc_code varchar(50) null,
start_date datetime null,
plant_plan_rule_type int null,
id int null
);
INSERT INTO plant_plan_rule_info (id, rc_code, start_date, plant_plan_rule_type) VALUES (14018, 'florescence', '2024-01-10 00:00:00', '0');
INSERT INTO plant_plan_rule_info (id, rc_code, start_date, plant_plan_rule_type) VALUES (14020, 'SEED', '2024-01-10 00:00:00', '0');
INSERT INTO plant_plan_rule_info (id, rc_code, start_date, plant_plan_rule_type) VALUES (14023, 'BOOT_STAGE', '2024-03-02 00:00:00', '0');
INSERT INTO plant_plan_rule_info (id, rc_code, start_date, plant_plan_rule_type) VALUES (14025, 'HEADING_AND_FILLING_PERIOD', '2024-05-05 00:00:00', '0');
INSERT INTO plant_plan_rule_info (id, rc_code, start_date, plant_plan_rule_type) VALUES (14027, 'SPRAY_STAGE', '2024-04-04 00:00:00', '0');
INSERT INTO plant_plan_rule_info (id, rc_code, start_date, plant_plan_rule_type) VALUES (14029, 'DRIVE_POWDER', '2024-05-05 00:00:00', '0');
INSERT INTO plant_plan_rule_info (id, rc_code, start_date, plant_plan_rule_type) VALUES (14031, 'MATERNAL_HARVESTING', '2024-06-07 00:00:00', '0');
INSERT INTO plant_plan_rule_info (id, rc_code, start_date, plant_plan_rule_type) VALUES (14033, 'florescence', '2024-01-10 00:00:00', '1');
INSERT INTO plant_plan_rule_info (id, rc_code, start_date, plant_plan_rule_type) VALUES (14035, 'BOOT_STAGE', '2024-03-02 00:00:00', '1');
INSERT INTO plant_plan_rule_info (id, rc_code, start_date, plant_plan_rule_type) VALUES (14037, 'HEADING_AND_FILLING_PERIOD', '2024-05-05 00:00:00', '1');
INSERT INTO plant_plan_rule_info (id, rc_code, start_date, plant_plan_rule_type) VALUES (14039, 'SPRAY_STAGE', '2024-04-04 00:00:00', '1');
INSERT INTO plant_plan_rule_info (id, rc_code, start_date, plant_plan_rule_type) VALUES (14041, 'DRIVE_POWDER', '2024-05-05 00:00:00', '1');
INSERT INTO plant_plan_rule_info (id, rc_code, start_date, plant_plan_rule_type) VALUES (14043, 'PATERNAL_CIRCUMCISION', '2024-06-05 00:00:00', '1');
INSERT INTO plant_plan_rule_info (id, rc_code, start_date, plant_plan_rule_type) VALUES (14159, 'SEED', '2024-02-02 00:00:00', '1');
不同 plant_plan_rule_type(只会为 0 1 )下,比较相同 rcCode 他们 在start_date 的差值。
要求:最后输出的结果如下:
SQL 实现
select rc_code, abs(DATEDIFF(maleStartDate, femaleStartDate)) as diffDay
from (select rc_code,
max(if(plant_plan_rule_type = 1, start_date, null)) as maleStartDate,
max(if(plant_plan_rule_type = 0, start_date, null)) as femaleStartDate
from plant_plan_rule_info i
group by rc_code) a;
思路
步骤一:想把它转化为一行,那么,先把每一行都要有个字段:maleStartDate 、femaleStartDate;
select rc_code,
if(plant_plan_rule_type = 1, start_date, null) as maleStartDate,
if(plant_plan_rule_type = 0, start_date, null) as femaleStartDate
from plant_plan_rule_info i
步骤二:再通过聚合函数 + 分组,就可以实现变为一行。
选择哪一种聚合函数:
min(): 那么, maleStartDate 、femaleStartDate 只会展示为0的,不符合预期sum():统计这一组值的和,不符合count():统计这一组值的个数,不符合- max() :符合,统计这一组中最大的值,那么我们就可以去除掉0了
select rc_code,
max(if(plant_plan_rule_type = 1, start_date, null)) as maleStartDate,
max(if(plant_plan_rule_type = 0, start_date, null)) as femaleStartDate
from plant_plan_rule_info i
group by rc_code;
步骤三:到这一步了,就没啥好说的了,搞个虚表,使用sql函数统计日期差值,为了避免出现负数,取绝对值就行
select rc_code, abs(DATEDIFF(maleStartDate, femaleStartDate)) as diffDay
from (select rc_code,
max(if(plant_plan_rule_type = 1, start_date, null)) as maleStartDate,
max(if(plant_plan_rule_type = 0, start_date, null)) as femaleStartDate
from plant_plan_rule_info i
group by rc_code) a;
小小延伸
要求:我现在要展示的列如下:
- rc_code : plant_plan_rule_info 的值
- malePlantPlanParentsRuleId : 当rc_code =1的时候,id的值:
- femalePlantPlanParentsRuleId:当rc_code =0的时候,id的值
- maleStartDate: 当rc_code =1的时候,start_date的值
- femaleStartDate: 当rc_code =0的时候,start_date的值
- diffDay:不同 plant_plan_rule_type(只会为 0 1 )下,比较相同 rcCode 他们 在start_date 的差值
最终展示的结果如下:
答案:
select rc_code,
malePlantPlanParentsRuleId,
femalePlantPlanParentsRuleId,
maleStartDate,
femaleStartDate,
abs(DATEDIFF(maleStartDate, femaleStartDate)) as diffDay
from (select rc_code,
max(if(plant_plan_rule_type = 1, id, 0)) as malePlantPlanParentsRuleId,
max(if(plant_plan_rule_type = 0, id, 0)) as femalePlantPlanParentsRuleId,
max(if(plant_plan_rule_type = 1, start_date, null)) as maleStartDate,
max(if(plant_plan_rule_type = 0, start_date, null)) as femaleStartDate
from plant_plan_rule_info i
group by rc_code) a;
为什么maleStartDate 和femaleStartDate 为null,而 malePlantPlanParentsRuleId 和 femalePlantPlanParentsRuleId 为0呢?
因为现在与Mybatis字段是实体类映射的时候,
maleStartDate
和femaleStartDate
Date无法映射为0,所以做null处理;
而malePlantPlanParentsRuleId
和femalePlantPlanParentsRuleId
为Long,可以映射为0,也可以进行null处理。