目錄

4.1 If函數語法格式

4.2 CASE WHEN 條件表達式格式

4.3 update與 case when

4.4 練習題1

4.5 練習題2

4.6 練習題3-行轉列

4.7 牛客練習題

4.8 LeetCode練習題

---

---

4.1 If函數語法格式

IF(expr1,expr2,expr3)

解釋：

如果表達式expr1=true(expr1 <> 0 and expr1 <> NULL),返回expr2,否則返回expr3，IF() 的返回值為數字值或字符串值，具體情況視其所在語境而定。

常用示例：

expr1參數為1，結果為真，返回正確

select if(1,'正確','錯誤');

結果：

4.2 CASE WHEN 條件表達式格式

1. 格式1：簡單case函數

**簡單CASE函數**
CASE 條件參數名稱WHEN 參數值1 THEN '顯示值1'WHEN 參數值2 THEN '顯示值2'...
ELSE '顯示其他值' END

2. 格式2：case搜索函數

CASE WHEN condition THEN result[WHEN...THEN...]
ELSE result
END

condition是一個返回布爾類型的表達式，

如果表達式返回true，則整個函數返回相應result的值，

如果表達式皆為false，則返回ElSE后result的值，

如果省略了ELSE子句，則返回NULL。

4.3 update與 case when

當我們有時候要更新 數據庫中 同一個字段 根據不同情況更新不同的值，可以用

update Table set  field = (case  XX  when  XXXX  then XXXwhen  xxxx then xxxxxxelse xxxx  end)

4.4 練習題1

數據庫

drop table if exists `students`;
create table students
(stu_code  varchar(10) null,stu_name  varchar(10) null,stu_sex   int         null,stu_score int         null
);
# 其中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);

場景1-不同狀態下展示為不同的值

題目：

現在學校想要根據學生分數（stu_score）劃分等級，score<60返回不及格，score>=60返回及格，score>=80返回優秀。請返回學生的分數（stu_score）和等級（score_cut)

根據示例，你的查詢應返回以下結果：

SQL代碼：

select stu_score,case when stu_score<60 then '不及格'when stu_score>=60 and stu_score<80 then '及格'when stu_score>=80 then '優秀'end as 'score_cut'
from students;

場景2- 統計不同狀態下的值

現老師要統計班中，有多少男同學，多少女同學，并統計男同學中有幾人及格，女同學中有幾人及格，要求用一個SQL輸出結果。其中stu_sex字段，0表示男生，1表示女生。

根據示例，你的查詢應返回以下結果：

select case when stu_sex=0 then '男'when stu_sex=1 then '女'end 'gender',count(*) as 'count_stu',sum(if(stu_score>=60,1,0)) as 'pass'
from students
group by gender;

場景3- update與case when相結合

請你編寫一個解決方案來交換所有的 '男' 和 '女' （即，將所有 '女' 變為 '男' ，反之亦然），僅使用 單個 update 語句 ，且不產生中間臨時表。其中stu_sex字段，0表示男生，1表示女生。

注意，你必須僅使用一條 update 語句，且 不能 使用 select 語句。

根據示例，你的查詢應返回以下結果：

update students set stu_sex=(case stu_sex when 0 then 1when 1 then 0end);

4.5 練習題2

數據庫

drop table if exists `energy_test`;
drop table if exists `p_price`;-- auto-generated definition
create table energy_test
(e_code  varchar(2)    null,e_value decimal(5, 2) null,e_type  int           null
);# 其中，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);-- 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 '電能耗單價表';INSERT INTO p_price (p_price, p_level, p_limit) VALUES (1.20, 0, 10);
INSERT INTO p_price (p_price, p_level, p_limit) VALUES (1.70, 1, 30);
INSERT INTO p_price (p_price, p_level, p_limit) VALUES (2.50, 2, 50);

energy_test

p_price

場景1 - 配合聚合函數做統計

現要求統計各個城市，總共使用了多少水耗、電耗、熱耗，使用一條SQL語句輸出結果 有能耗表如下：其中，E_TYPE表示能耗類型，0表示水耗，1表示電耗，2表示熱耗

根據示例，你的查詢應返回以下結果：

select e_code,sum(if(e_type=0,e_value,0)) as '水耗',sum(if(e_type=1,e_value,0)) as '電耗',sum(if(e_type=2,e_value,0)) as '熱耗'
from energy_test
group by e_code;

場景2-使用子查詢

根據城市用電量多少，計算用電成本。假設電能耗單價分為三檔，根據不同的能耗值，使用相應價格計算成本。 P_limit為每個檔次的上限。當能耗值（e_value)小于10時，使用P_LEVEL=0時的P_PRICE的值，能耗值大于10小于30使用P_LEVEL=1時的P_PRICE的值…

根據示例，你的查詢應返回以下結果：

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) then (select p_price from p_price where p_level=2)end as 'price'
from energy_test
where e_type=1;

4.6 練習題3-行轉列

數據庫

drop table if exists `user_col_comments`;-- auto-generated definition
create table user_col_comments
(column_name varchar(50)  null comment '列名',comment     varchar(100) null comment '列的備注'
);INSERT INTO user_col_comments (column_name, comment) VALUES ('SHI_SHI_CODE', '設施編號');
INSERT INTO user_col_comments (column_name, comment) VALUES ('SHUI_HAO', '水耗');
INSERT INTO user_col_comments (column_name, comment) VALUES ('RE_HAO', '熱耗');
INSERT INTO user_col_comments (column_name, comment) VALUES ('YAN_HAO', '鹽耗');
INSERT INTO user_col_comments (column_name, comment) VALUES ('OTHER', '其他');

問題:將上表的行列進行轉換顯示

根據示例，你的查詢應返回以下結果：

select max(if(column_name='SHI_SHI_CODE',comment,'')) as 'SHI_SHI_CODE',max(if(column_name='YAN_HAO',comment,'')) as 'YAN_HAO',max(if(column_name='RE_HAO',comment,'')) as 'RE_HAO',max(if(column_name='SHUI_HAO',comment,'')) as 'SHUI_HAO',max(if(column_name='OTHER',comment,'')) as 'OTHER'
from user_col_comments;

4.7 牛客練習題

SQL26 - 計算25歲以上和以下的用戶數量

4.8 LeetCode練習題

• LeetCode_1873. 計算特殊獎金

• LeetCode_627. 變更性別

• LeetCode_608. 樹節點

• LeetCode_1393. 股票的資本損益