基礎(chǔ)知識

當(dāng)我們需要從數(shù)據(jù)源上直接判斷數(shù)據(jù)顯示代表的含義的時候 ,就可以在SQL語句中使用 Case When這個函數(shù)了.

Case具有兩種格式。簡單Case函數(shù)和Case搜索函數(shù)。
簡單函數(shù)

CASE [col_name] WHEN [value1] THEN [result1]…ELSE [default] END

搜索函數(shù)

CASE WHEN [expr] THEN [result1]…ELSE [default] END

用法介紹

簡單Case函數(shù)

格式說明

　case 列名
　　　　when   條件值1   then  選擇項1
　　　　when   條件值2    then  選項2.......
　　　　else     默認(rèn)值
    end

案例1:

SELECT
CASE job_level 　　 
    WHEN '1' THEN   '1111' 　　 
        WHEN　 '2' THEN  '1111' 　　 
        WHEN　 '3' THEN  '1111' 　 
        ELSE 'eee' 
END 
FROM
    employee

Case搜索函數(shù)

格式說明

case  
　　　　when  列名= 條件值1   then  選擇項1
　　　　when  列名=條件值2    then  選項2.......
　　　　else    默認(rèn)值 
end

案例2:

UPDATE employee 　　　　 
SET e_wage = 　　　　 
CASE　　 
    WHEN job_level = '1' THEN e_wage * 1.97 　　　　 
    WHEN job_level = '2' THEN e_wage * 1.07 　　　　 
    WHEN job_level = '3' THEN e_wage * 1.06 　　　　 
    ELSE e_wage * 1.05 　　　　 
END

使用場景

場景1

場景1：有分?jǐn)?shù)score，score<60返回不及格，score>=60返回及格，score>=80返回優(yōu)秀


SELECT
    STUDENT_NAME,
    (CASE WHEN score < 60 THEN '不及格'
        WHEN score >= 60 AND score < 80 THEN '及格'
        WHEN score >= 80 THEN '優(yōu)秀'
        ELSE '異常' END) AS REMARK
FROM
    TABLE

注意：如果你想判斷score是否null的情況，WHEN score = null THEN '缺席考試'，這是一種錯誤的寫法，正確的寫法應(yīng)為：

CASE WHEN score IS NULL THEN '缺席考試' ELSE '正常' END

案例2：

現(xiàn)老師要統(tǒng)計班中，有多少男同學(xué)，多少女同學(xué)，并統(tǒng)計男同學(xué)中有幾人及格，女同學(xué)中有幾人及格，要求用一個SQL輸出結(jié)果。
表結(jié)構(gòu)如下：其中STU_SEX字段，0表示男生，1表示女生。

STU_CODE	STU_NAME	STU_SEX	STU_SCORE
XM	小明	0	88
XL	小磊	0	55
XF	小峰	0	45
XH	小紅	1	66
XN	曉妮	1	77
XY	小伊	1	99

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 
    THTF_STUDENTS

輸出結(jié)果如下：

MALE_COUNT	FEMALE_COUNT	MALE_PASS	FEMALE_PASS
3	3	1	3

場景3：

經(jīng)典行轉(zhuǎn)列，并配合聚合函數(shù)做統(tǒng)計：現(xiàn)要求統(tǒng)計各個城市，總共使用了多少水耗、電耗、熱耗，使用一條SQL語句輸出結(jié)果

有能耗表如下：其中，E_TYPE表示能耗類型，0表示水耗，1表示電耗，2表示熱耗

E_CODE	E_VALUE	E_TYPE
北京	28.50	0
北京	23.51	1
北京	28.12	2
北京	12.30	0
北京	15.46	1
上海	18.88	0
上海	16.66	1
上海	19.99	0
上海	10.05	0

SELECT 
    E_CODE,
    SUM(CASE WHEN E_TYPE = 0 THEN E_VALUE ELSE 0 END) AS WATER_ENERGY,--水耗
    SUM(CASE WHEN E_TYPE = 1 THEN E_VALUE ELSE 0 END) AS ELE_ENERGY,--電耗
    SUM(CASE WHEN E_TYPE = 2 THEN E_VALUE ELSE 0 END) AS HEAT_ENERGY--熱耗
FROM 
    THTF_ENERGY_TEST
GROUP BY
    E_CODE

輸出結(jié)果如下：

E_CODE	WATER_ENERGY	ELE_ENERGY	HEAT_ENERGY
北京	40.80	38.97	28.12
上海	48.92	16.66	0

場景4：

CASE WHEN中使用子查詢：根據(jù)城市用電量多少，計算用電成本。假設(shè)電能耗單價分為三檔，根據(jù)不同的能耗值，使用相應(yīng)價格計算成本。當(dāng)能耗值小于10時，使用P_LEVEL=0時的P_PRICE的值，能耗值大于10小于30使用P_LEVEL=1時的P_PRICE的值...

價格表如下：

P_PRICE	P_LEVEL	P_LIMIT
1.20	0	10
1.70	1	30
2.50	2	50

CASE WHEN energy <= (SELECT P_LIMIT FROM TABLE_PRICE WHERE P_LEVEL = 0) THEN (SELECT P_PRICE FROM TABLE_PRICE WHERE P_LEVEL = 0)
    WHEN energy > (SELECT P_LIMIT FROM TABLE_PRICE WHERE P_LEVEL = 0) AND energy <= (SELECT P_LIMIT FROM TABLE_PRICE WHERE P_LEVEL = 1) THEN (SELECT P_PRICE FROM TABLE_PRICE WHERE P_LEVEL = 1)
    WHEN energy > (SELECT P_LIMIT FROM TABLE_PRICE WHERE P_LEVEL = 1) AND energy <= (SELECT P_LIMIT FROM TABLE_PRICE WHERE P_LEVEL = 2) THEN (SELECT P_PRICE FROM TABLE_PRICE WHERE P_LEVEL = 2)

練習(xí)題

練習(xí)題1

已經(jīng)有一張表名為scores的學(xué)生成績表，表內(nèi)的數(shù)據(jù)如下：

id	class_id	student_id	score
1	1	1	100
2	1	1	90
3	1	1	80
4	1	2	70
5	1	2	60
6	2	3	50

平均得分與等級之間的轉(zhuǎn)換關(guān)系如下表：

得分	等級
>=90	A
80-90	B
70-80	C
60-70	D
<60	E

請寫出班級號為1的學(xué)生的學(xué)號及各科平均成績其對應(yīng)的等級，查詢結(jié)果按照學(xué)生編號順序排列。

查詢結(jié)果示例：

student_id	LEVEL
1	A
2	D

SELECT
    temp.student_id,
    (
            CASE
                WHEN ( temp.avg_score >= 90 ) THEN 'A' 
                WHEN ( temp.avg_score >= 80 AND temp.avg_score < 90 ) THEN 'B' 
                WHEN ( temp.avg_score >= 70 AND temp.avg_score < 80 ) THEN 'C' 
                WHEN ( temp.avg_score >= 60 AND temp.avg_score < 70 ) THEN 'D'  
                WHEN ( temp.avg_score < 60 ) THEN 'E' 
            END 
    ) AS LEVEL 
    FROM
        ( SELECT student_id, AVG( score ) AS avg_score FROM scores WHERE class_id = 1 GROUP BY student_id ) AS temp 
    ORDER BY
        temp.student_id ASC;

聯(lián)系題2：

首先，向數(shù)據(jù)庫中插入如下的測試數(shù)據(jù)，code字段表示稅號、name字段表示企業(yè)名稱，value字段表示稅后額，month字段表示月份。現(xiàn)在要求合并所有企業(yè)5月和6月的稅收結(jié)果在同一張表中，請寫出SQL查詢語句。

/*
Navicat MySQL Data Transfer
Date: 2019-07-04 20:11:31
*/

SET FOREIGN_KEY_CHECKS=0;

-- ----------------------------
-- Table structure for tax
-- ----------------------------
DROP TABLE IF EXISTS `tax`;
CREATE TABLE `tax` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `code` varchar(255) NOT NULL,
  `name` varchar(255) NOT NULL,
  `value` double(20,2) NOT NULL,
  `month` int(11) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1612 DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of tax
-- ----------------------------
INSERT INTO `tax` VALUES ('1', '913301225802604739', '浙江萬拓房地產(chǎn)有限公司', '8217325.31', '5');
INSERT INTO `tax` VALUES ('2', '913301000980647063', '杭州市桐廬縣龍生小額貸款股份有限公司', '4687443.89', '5');
INSERT INTO `tax` VALUES ('3', '91330122557922045C', '浙江通泰房地產(chǎn)有限公司', '3298898.23', '5');
INSERT INTO `tax` VALUES ('4', '9133012267397706XA', '杭州杭千高速石油發(fā)展有限公司', '2749974.69', '5');
INSERT INTO `tax` VALUES ('182', '91330122754412626Y', '桐廬明晟五金塑料制品廠', '59973.47', '5');
INSERT INTO `tax` VALUES ('183', '91330122092047374A', '桐廬申翔貿(mào)易有限公司', '59791.88', '5');
INSERT INTO `tax` VALUES ('184', '91330122560585828H', '桐廬玉翰置業(yè)有限公司', '59447.14', '5');
INSERT INTO `tax` VALUES ('185', '91330122328150053L', '杭州沃科特生物科技有限公司', '59432.87', '5');
INSERT INTO `tax` VALUES ('186', '913301227766368233', '桐廬璟泰帽廠', '59378.73', '5');
INSERT INTO `tax` VALUES ('187', '913301227572167865', '桐廬中新箱包有限公司', '59088.56', '5');
INSERT INTO `tax` VALUES ('188', '91330122MA28XGUR3R', '浙江盛添醫(yī)療器械有限公司', '58478.49', '5');
INSERT INTO `tax` VALUES ('189', '91330122MA28WGAT7Y', '杭州商旅經(jīng)營發(fā)展有限公司桐廬北區(qū)分公司', '58393.21', '5');
INSERT INTO `tax` VALUES ('190', '91330122694560706A', '杭州拓樂實業(yè)有限公司', '58175.11', '5');
INSERT INTO `tax` VALUES ('191', '91330108082110405A', '杭州豐展貿(mào)易有限公司', '58170.71', '5');
INSERT INTO `tax` VALUES ('192', '91330122MA2B1RUM0C', '杭州康怡科技貿(mào)易有限公司', '58075.73', '5');
INSERT INTO `tax` VALUES ('319', '92330122MA28WW5Q4T', '桐廬縣江南鎮(zhèn)芬花景觀石經(jīng)營部', '22455.33', '5');
INSERT INTO `tax` VALUES ('320', '92330122MA280M4U9C', '桐廬縣江南鎮(zhèn)增娟箱包廠', '22447.62', '5');
INSERT INTO `tax` VALUES ('321', '91330122568778667U', '杭州泓威焊錫材料有限公司', '22303.81', '5');
INSERT INTO `tax` VALUES ('322', '91330122MA28NULH8J', '杭州三石醫(yī)療器械有限公司', '22194.10', '5');
INSERT INTO `tax` VALUES ('323', '91330122MA2B2G9L48', '杭州恒輝醫(yī)療設(shè)備有限公司', '22186.01', '5');
INSERT INTO `tax` VALUES ('324', '913301220567142056', '杭州萬星物業(yè)管理有限公司桐廬分公司', '22058.55', '5');
INSERT INTO `tax` VALUES ('336', '91330122673967021P', '桐廬中遠(yuǎn)塑業(yè)有限公司', '19202.37', '5');
INSERT INTO `tax` VALUES ('337', '91330122609247142P', '杭州永澳經(jīng)貿(mào)有限公司', '19149.02', '5');
INSERT INTO `tax` VALUES ('338', '91330122MA2B29WT71', '桐廬偉恩醫(yī)療器械有限公司', '18991.58', '5');
INSERT INTO `tax` VALUES ('339', '91330122MA2CG7UL00', '杭州潤霖醫(yī)療器械有限公司', '18643.14', '5');
INSERT INTO `tax` VALUES ('340', '92330122MA2BJYUR91', '桐廬縣江南鎮(zhèn)樂洲橡塑制品商行', '18454.37', '5');
INSERT INTO `tax` VALUES ('341', '9133012206786740X3', '桐廬巨索起重設(shè)備有限公司', '18388.92', '5');
INSERT INTO `tax` VALUES ('342', '92330122MA2CFCR32J', '杭州佳羅醫(yī)療器械有限公司', '18343.51', '5');
INSERT INTO `tax` VALUES ('1416', '91330104MA28RB2N6W', '杭州均勝信息科技有限公司', '1361.48', '6');
INSERT INTO `tax` VALUES ('1417', '91330122MA2CF0HM11', '杭州正道工匠文化藝術(shù)有限公司', '1250.00', '6');
INSERT INTO `tax` VALUES ('1418', '91330122577317395K', '杭州邦輝摩擦材料有限公司', '1249.95', '6');
INSERT INTO `tax` VALUES ('1419', '91330104MA2B1BYA47', '杭州依尼歐貿(mào)易有限公司', '1200.31', '6');
INSERT INTO `tax` VALUES ('1420', '92330122MA2CC2256Y', '桐廬縣江南鎮(zhèn)劍峰五金加工廠', 
INSERT INTO `tax` VALUES ('1610', '91330122MA28WYUM6C', '杭州合鑫醫(yī)療器械有限公司', '-26209.98', '6');
INSERT INTO `tax` VALUES ('1611', '913301227265865731', '杭州環(huán)宇針織有限公司', '-340376.70', '6');

下圖為表中部分?jǐn)?shù)據(jù)：

表中部分?jǐn)?shù)據(jù)

select code,name,
sum(case when month=5 then value else 0 end) as month_value_5,
sum(case when month=6 then value else 0 end) as month_value_6
from 
tax
GROUP BY
code;

下圖給出了部分查詢結(jié)果：

結(jié)果示例

色偷偷精品伊人,欧洲久久精品,欧美综合婷婷骚逼,国产AV主播,国产最新探花在线,九色在线视频一区,伊人大交九欧美,1769亚洲,黄色成人av

【MySQL入門篇第六節(jié)】case when then else end

【MySQL入門篇第六節(jié)】case when then else end

基礎(chǔ)知識

用法介紹

簡單Case函數(shù)

Case搜索函數(shù)

使用場景

場景1

案例2：

場景3：

場景4：

練習(xí)題

練習(xí)題1

聯(lián)系題2：

相關(guān)閱讀更多精彩內(nèi)容

友情鏈接更多精彩內(nèi)容

色偷偷精品伊人,欧洲久久精品,欧美综合婷婷骚逼,国产AV主播,国产最新探花在线,九色在线视频一区,伊人大交九 欧美,1769亚洲,黄色成人av

【MySQL入門篇 第六節(jié)】case when then else end

基礎(chǔ)知識

用法介紹

簡單Case函數(shù)

Case搜索函數(shù)

使用場景

場景1

案例2：

場景3：

場景4：

練習(xí)題

練習(xí)題1

聯(lián)系題2：

相關(guān)閱讀更多精彩內(nèi)容

友情鏈接更多精彩內(nèi)容

色偷偷精品伊人,欧洲久久精品,欧美综合婷婷骚逼,国产AV主播,国产最新探花在线,九色在线视频一区,伊人大交九欧美,1769亚洲,黄色成人av

【MySQL入門篇第六節(jié)】case when then else end