百度seo网站,惠州百优做网站小程序熊掌号,软件开发模型是对软件开发过程的一种规范描述,注册个公司大概要多少钱目录
一、简单CASE WHEN函数#xff1a;二、CASE WHEN条件表达式函数三、常用场景 场景1#xff1a;不同状态展示为不同的值场景2#xff1a;统计不同状态下的值场景3#xff1a;配合聚合函数做统计场景4#xff1a;CASE WHEN中使用子查询场景5#xff1a;经典行转列二、CASE WHEN条件表达式函数三、常用场景 场景1不同状态展示为不同的值场景2统计不同状态下的值场景3配合聚合函数做统计场景4CASE 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 resultENDSQL语言演示
CASE WHEN SCORE A THEN 优WHEN SCORE B THEN 良WHEN SCORE C THEN 中 ELSE 不及格 END# 等同于
CASE scoreWHEN A THEN 优WHEN B THEN 良WHEN C THEN 中ELSE 不及格 ENDcondition是一个返回布尔类型的表达式 如果表达式返回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不同状态展示为不同的值 有分数scorescore60返回不及格score60返回及格score80返回优秀 # 有分数scorescore60返回不及格score60返回及格score80返回优秀
SELECTstu_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表示女生。 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_PASS
FROMstudents;输出结果如下 注意点
用的是 sum 而不是countTHEN 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;输出结果如下 场景4CASE WHEN中使用子查询 根据城市用电量多少计算用电成本。假设电能耗单价分为三档根据不同的能耗值使用相应价格计算成本。 当能耗值小于10时使用P_LEVEL0时的P_PRICE的值能耗值大于10小于30使用P_LEVEL1时的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也行。 selectmax(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;输出结果如下
