清華asp_動(dòng)態(tài)網(wǎng)站開發(fā)石家莊seo全網(wǎng)營(yíng)銷
目錄
- 一、簡(jiǎn)單CASE WHEN函數(shù)：
- 二、CASE WHEN條件表達(dá)式函數(shù)
- 三、常用場(chǎng)景
-
- 場(chǎng)景1：不同狀態(tài)展示為不同的值
- 場(chǎng)景2：統(tǒng)計(jì)不同狀態(tài)下的值
- 場(chǎng)景3：配合聚合函數(shù)做統(tǒng)計(jì)
- 場(chǎng)景4：CASE WHEN中使用子查詢
- 場(chǎng)景5：經(jīng)典行轉(zhuǎn)列，結(jié)合max聚合函數(shù)
一、簡(jiǎn)單CASE WHEN函數(shù)：
CASE SCORE WHEN 'A' THEN '優(yōu)' ELSE '不及格' END# 使用 IF 函數(shù)進(jìn)行替換
IF(SCORE = 'A', '優(yōu)', '不及格')
THEN后邊的值與ELSE后邊的值類型應(yīng)一致，否則會(huì)報(bào)錯(cuò)。
如下：
CASE SCORE WHEN ‘A’ THEN ‘優(yōu)’ ELSE 0 END’優(yōu)’和0數(shù)據(jù)類型不一致則報(bào)錯(cuò)：
[Err] ORA-00932: 數(shù)據(jù)類型不一致: 應(yīng)為 CHAR, 但卻獲得 NUMBER
簡(jiǎn)單CASE WHEN函數(shù)只能應(yīng)對(duì)一些簡(jiǎn)單的業(yè)務(wù)場(chǎng)景，而CASE WHEN條件表達(dá)式的寫法則更加靈活。
二、CASE WHEN條件表達(dá)式函數(shù)
類似JAVA中的IF ELSE語(yǔ)句。
格式：
CASE WHEN condition THEN result[WHEN...THEN...]ELSE resultEND
SQL語(yǔ)言演示：
CASE WHEN SCORE = 'A' THEN '優(yōu)'WHEN SCORE = 'B' THEN '良'WHEN SCORE = 'C' THEN '中' ELSE '不及格' END# 等同于
CASE scoreWHEN 'A' THEN '優(yōu)'WHEN 'B' THEN '良'WHEN 'C' THEN '中'ELSE '不及格' END
condition是一個(gè)返回布爾類型的表達(dá)式，
如果表達(dá)式返回true，則整個(gè)函數(shù)返回相應(yīng)result的值，
如果表達(dá)式皆為false，則返回ElSE后result的值，如果省略了ELSE子句，則返回NULL。
三、常用場(chǎng)景
前言
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', '小時(shí)', 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 '價(jià)格',p_level int null comment '等級(jí)',p_limit int null comment '閾值'
)comment '電能耗單價(jià)表';
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', '設(shè)施編號(hào)');
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', '其他');
場(chǎng)景1：不同狀態(tài)展示為不同的值
有分?jǐn)?shù)score，score<60返回不及格，score>=60返回及格，score>=80返回優(yōu)秀
# 有分?jǐn)?shù)score，score<60返回不及格，score>=60返回及格，score>=80返回優(yōu)秀
SELECTstu_name,(CASE WHEN stu_score < 60 THEN '不及格'WHEN stu_score >= 60 AND stu_score < 80 THEN '及格'WHEN stu_score >= 80 THEN '優(yōu)秀'ELSE '異常' END) AS REMARK
FROM students;
注意：如果你想判斷score是否null的情況，WHEN score = null THEN ‘缺席考試’，這是一種錯(cuò)誤的寫法，正確的寫法應(yīng)為：CASE WHEN score IS NULL THEN '缺席考試' ELSE '正常' END
場(chǎng)景2：統(tǒng)計(jì)不同狀態(tài)下的值
現(xiàn)老師要統(tǒng)計(jì)班中，有多少男同學(xué)，多少女同學(xué)，并統(tǒng)計(jì)男同學(xué)中有幾人及格，女同學(xué)中有幾人及格，要求用一個(gè)SQL輸出結(jié)果。其中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;
輸出結(jié)果如下：
注意點(diǎn)：
- 用的是 ：
sum?而不是count THEN 1 ELSE 0的位置不能改變：否則會(huì)有以下效果：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’ 和 數(shù)值 0，使用 都是一樣的
場(chǎng)景3：配合聚合函數(shù)做統(tǒng)計(jì)
現(xiàn)要求統(tǒng)計(jì)各個(gè)城市，總共使用了多少水耗、電耗、熱耗，使用一條SQL語(yǔ)句輸出結(jié)果
有能耗表如下：其中，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;
輸出結(jié)果如下：
場(chǎng)景4：CASE WHEN中使用子查詢
根據(jù)城市用電量多少，計(jì)算用電成本。假設(shè)電能耗單價(jià)分為三檔，根據(jù)不同的能耗值，使用相應(yīng)價(jià)格計(jì)算成本。
當(dāng)能耗值小于10時(shí)，使用P_LEVEL=0時(shí)的P_PRICE的值，能耗值大于10小于30使用P_LEVEL=1時(shí)的P_PRICE的值…
energy_test 我修改了e_type 為1的值的兩條數(shù)據(jù)的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;
輸出結(jié)果如下：
場(chǎng)景5：經(jīng)典行轉(zhuǎn)列，結(jié)合max聚合函數(shù)
行轉(zhuǎn)列中 SUM作用：無(wú)用，但是select后得跟聚合函數(shù)，不能去掉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;
輸出結(jié)果如下：