【MySQL入门篇 第六节】case when then else end

基础知识

当我们需要从数据源上 直接判断数据显示代表的含义的时候 ,就可以在SQL语句中使用 Case When这个函数了.

Case具有两种格式。简单Case函数和Case搜索函数。
简单函数

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

搜索函数

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

用法介绍

简单Case函数

格式说明

　case 列名
　　　　when   条件值1   then  选择项1
　　　　when   条件值2    then  选项2.......
　　　　else     默认值
    end

案例1:

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

Case搜索函数

格式说明

case  
　　　　when  列名= 条件值1   then  选择项1
　　　　when  列名=条件值2    then  选项2.......
　　　　else    默认值 
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：有分数score，score<60返回不及格，score>=60返回及格，score>=80返回优秀


SELECT
    STUDENT_NAME,
    (CASE WHEN score < 60 THEN '不及格'
        WHEN score >= 60 AND score < 80 THEN '及格'
        WHEN score >= 80 THEN '优秀'
        ELSE '异常' END) AS REMARK
FROM
    TABLE

注意：如果你想判断score是否null的情况，WHEN score = null THEN '缺席考试'，这是一种错误的写法，正确的写法应为：

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

案例2：

现老师要统计班中，有多少男同学，多少女同学，并统计男同学中有几人及格，女同学中有几人及格，要求用一个SQL输出结果。
表结构如下：其中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

输出结果如下：

MALE_COUNT FEMALE_COUNT MALE_PASS FEMALE_PASS
3 3 1 3

场景3：

经典行转列，并配合聚合函数做统计：现要求统计各个城市，总共使用了多少水耗、电耗、热耗，使用一条SQL语句输出结果

有能耗表如下：其中，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

输出结果如下：

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

场景4：

CASE WHEN中使用子查询：根据城市用电量多少，计算用电成本。假设电能耗单价分为三档，根据不同的能耗值，使用相应价格计算成本。当能耗值小于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)

练习题

练习题1

已经有一张表名为scores的学生成绩表，表内的数据如下：

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

平均得分与等级之间的转换关系如下表：

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

请写出班级号为1的学生的学号及各科平均成绩其对应的等级，查询结果按照学生编号顺序排列。

查询结果示例：

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;

联系题2：

首先，向数据库中插入如下的测试数据，code字段表示税号、name字段表示企业名称，value字段表示税后额，month字段表示月份。现在要求合并所有企业5月和6月的税收结果在同一张表中，请写出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', '浙江万拓房地产有限公司', '8217325.31', '5');
INSERT INTO `tax` VALUES ('2', '913301000980647063', '杭州市桐庐县龙生小额贷款股份有限公司', '4687443.89', '5');
INSERT INTO `tax` VALUES ('3', '91330122557922045C', '浙江通泰房地产有限公司', '3298898.23', '5');
INSERT INTO `tax` VALUES ('4', '9133012267397706XA', '杭州杭千高速石油发展有限公司', '2749974.69', '5');
INSERT INTO `tax` VALUES ('182', '91330122754412626Y', '桐庐明晟五金塑料制品厂', '59973.47', '5');
INSERT INTO `tax` VALUES ('183', '91330122092047374A', '桐庐申翔贸易有限公司', '59791.88', '5');
INSERT INTO `tax` VALUES ('184', '91330122560585828H', '桐庐玉翰置业有限公司', '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', '浙江盛添医疗器械有限公司', '58478.49', '5');
INSERT INTO `tax` VALUES ('189', '91330122MA28WGAT7Y', '杭州商旅经营发展有限公司桐庐北区分公司', '58393.21', '5');
INSERT INTO `tax` VALUES ('190', '91330122694560706A', '杭州拓乐实业有限公司', '58175.11', '5');
INSERT INTO `tax` VALUES ('191', '91330108082110405A', '杭州丰展贸易有限公司', '58170.71', '5');
INSERT INTO `tax` VALUES ('192', '91330122MA2B1RUM0C', '杭州康怡科技贸易有限公司', '58075.73', '5');
INSERT INTO `tax` VALUES ('319', '92330122MA28WW5Q4T', '桐庐县江南镇芬花景观石经营部', '22455.33', '5');
INSERT INTO `tax` VALUES ('320', '92330122MA280M4U9C', '桐庐县江南镇增娟箱包厂', '22447.62', '5');
INSERT INTO `tax` VALUES ('321', '91330122568778667U', '杭州泓威焊锡材料有限公司', '22303.81', '5');
INSERT INTO `tax` VALUES ('322', '91330122MA28NULH8J', '杭州三石医疗器械有限公司', '22194.10', '5');
INSERT INTO `tax` VALUES ('323', '91330122MA2B2G9L48', '杭州恒辉医疗设备有限公司', '22186.01', '5');
INSERT INTO `tax` VALUES ('324', '913301220567142056', '杭州万星物业管理有限公司桐庐分公司', '22058.55', '5');
INSERT INTO `tax` VALUES ('336', '91330122673967021P', '桐庐中远塑业有限公司', '19202.37', '5');
INSERT INTO `tax` VALUES ('337', '91330122609247142P', '杭州永澳经贸有限公司', '19149.02', '5');
INSERT INTO `tax` VALUES ('338', '91330122MA2B29WT71', '桐庐伟恩医疗器械有限公司', '18991.58', '5');
INSERT INTO `tax` VALUES ('339', '91330122MA2CG7UL00', '杭州润霖医疗器械有限公司', '18643.14', '5');
INSERT INTO `tax` VALUES ('340', '92330122MA2BJYUR91', '桐庐县江南镇乐洲橡塑制品商行', '18454.37', '5');
INSERT INTO `tax` VALUES ('341', '9133012206786740X3', '桐庐巨索起重设备有限公司', '18388.92', '5');
INSERT INTO `tax` VALUES ('342', '92330122MA2CFCR32J', '杭州佳罗医疗器械有限公司', '18343.51', '5');
INSERT INTO `tax` VALUES ('1416', '91330104MA28RB2N6W', '杭州均胜信息科技有限公司', '1361.48', '6');
INSERT INTO `tax` VALUES ('1417', '91330122MA2CF0HM11', '杭州正道工匠文化艺术有限公司', '1250.00', '6');
INSERT INTO `tax` VALUES ('1418', '91330122577317395K', '杭州邦辉摩擦材料有限公司', '1249.95', '6');
INSERT INTO `tax` VALUES ('1419', '91330104MA2B1BYA47', '杭州依尼欧贸易有限公司', '1200.31', '6');
INSERT INTO `tax` VALUES ('1420', '92330122MA2CC2256Y', '桐庐县江南镇剑峰五金加工厂', 
INSERT INTO `tax` VALUES ('1610', '91330122MA28WYUM6C', '杭州合鑫医疗器械有限公司', '-26209.98', '6');
INSERT INTO `tax` VALUES ('1611', '913301227265865731', '杭州环宇针织有限公司', '-340376.70', '6');

下图为表中部分数据：


表中部分数据
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;

下图给出了部分查询结果：


结果示例
最后编辑于 ：
©著作权归作者所有,转载或内容合作请联系作者
  • 人面猴
    序言：七十年代末，一起剥皮案震惊了整个滨河市，随后出现的几起案子，更是在滨河造成了极大的恐慌，老刑警刘岩，带你破解...
    沈念sama阅读 219,366评论 6赞 508
  • 死咒
    序言：滨河连续发生了三起死亡事件，死亡现场离奇诡异，居然都是意外死亡，警方通过查阅死者的电脑和手机，发现死者居然都...
    沈念sama阅读 93,521评论 3赞 395
  • 救了他两次的神仙让他今天三更去死
    文/潘晓璐 我一进店门，熙熙楼的掌柜王于贵愁眉苦脸地迎上来，“玉大人，你说我怎么就摊上这事。” “怎么了？”我有些...
    开封第一讲书人阅读 165,689评论 0赞 356
  • 道士缉凶录：失踪的卖姜人
    文/不坏的土叔 我叫张陵，是天一观的道长。 经常有香客问我，道长，这世上最难降的妖魔是什么？ 我笑而不...
    开封第一讲书人阅读 58,925评论 1赞 295
  • 港岛之恋（遗憾婚礼）
    正文 为了忘掉前任，我火速办了婚礼，结果婚礼上，老公的妹妹穿的比我还像新娘。我一直安慰自己，他们只是感情好，可当我...
    茶点故事阅读 67,942评论 6赞 392
  • 恶毒庶女顶嫁案：这布局不是一般人想出来的
    文/花漫 我一把揭开白布。 她就那样静静地躺着，像睡着了一般。 火红的嫁衣衬着肌肤如雪。 梳的纹丝不乱的头发上，一...
    开封第一讲书人阅读 51,727评论 1赞 305
  • 城市分裂传说
    那天，我揣着相机与录音，去河边找鬼。 笑死，一个胖子当着我的面吹牛，可吹牛的内容都是我干的。 我是一名探鬼主播，决...
    沈念sama阅读 40,447评论 3赞 420
  • 双鸳鸯连环套：你想象不到人心有多黑
    文/苍兰香墨 我猛地睁开眼，长吁一口气：“原来是场噩梦啊……” “哼！你这毒妇竟也来了？” 一声冷哼从身侧响起，我...
    开封第一讲书人阅读 39,349评论 0赞 276
  • 万荣杀人案实录
    序言：老挝万荣一对情侣失踪，失踪者是张志新（化名）和其女友刘颖，没想到半个月后，有当地人在树林里发现了一具尸体，经...
    沈念sama阅读 45,820评论 1赞 317
  • 护林员之死
    正文 独居荒郊野岭守林人离奇死亡，尸身上长有42处带血的脓包…… 初始之章·张勋 以下内容为张勋视角 年9月15日...
    茶点故事阅读 37,990评论 3赞 337
  • 白月光启示录
    正文 我和宋清朗相恋三年，在试婚纱的时候发现自己被绿了。 大学时的朋友给我发了我未婚夫和他白月光在一起吃饭的照片。...
    茶点故事阅读 40,127评论 1赞 351
  • 活死人
    序言：一个原本活蹦乱跳的男人离奇死亡，死状恐怖，灵堂内的尸体忽然破棺而出，到底是诈尸还是另有隐情，我是刑警宁泽，带...
    沈念sama阅读 35,812评论 5赞 346
  • 日本核电站爆炸内幕
    正文 年R本政府宣布，位于F岛的核电站，受9级特大地震影响，放射性物质发生泄漏。R本人自食恶果不足惜，却给世界环境...
    茶点故事阅读 41,471评论 3赞 331
  • 男人毒药：我在死后第九天来索命
    文/蒙蒙 一、第九天 我趴在偏房一处隐蔽的房顶上张望。 院中可真热闹，春花似锦、人声如沸。这庄子的主人今日做“春日...
    开封第一讲书人阅读 32,017评论 0赞 22
  • 一桩弑父案，背后竟有这般阴谋
    文/苍兰香墨 我抬头看了看天上的太阳。三九已至，却和暖如春，着一层夹袄步出监牢的瞬间，已是汗流浃背。 一阵脚步声响...
    开封第一讲书人阅读 33,142评论 1赞 272
  • 情欲美人皮
    我被黑心中介骗来泰国打工， 没想到刚下飞机就差点儿被人妖公主榨干…… 1. 我叫王不留，地道东北人。 一个月前我还...
    沈念sama阅读 48,388评论 3赞 373
  • 代替公主和亲
    正文 我出身青楼，却偏偏与公主长得像，于是被迫代替她去往敌国和亲。 传闻我的和亲对象是个残疾皇子，可洞房花烛夜当晚...
    茶点故事阅读 45,066评论 2赞 355

推荐阅读更多精彩内容

  • MYSQL基础
    MYSQL 基础知识 1 MySQL数据库概要 2 简单MySQL环境 3 数据的存储和获取 4 MySQL基本操...
    Kingtester阅读 7,818评论 5赞 116
  • oracle错误集合
    ORA-00001: 违反唯一约束条件 (.) 错误说明：当在唯一索引所对应的列上键入重复值时，会触发此异常。 O...
    我想起个好名字阅读 5,320评论 0赞 9
  • SQL中的case when then else end用法
    Case具有两种格式。简单Case函数和Case搜索函数。 这两种方式，可以实现相同的功能。简单Case函数的写法...
    金星show阅读 7,744评论 1赞 7
  • 夜空中最亮的星
    在深冬凄冷的清晨 一个人踏上孤独的旅程 高速列车穿越时空 窗外的阳光温暖晴明 耳机中想起这首歌 让我想起过往和曾经...
    骑驴去酒吧阅读 325评论 0赞 6
  • 胡思乱语·十月
    一生狗奴才，偶尔山大王。2013-10-31# 看央视转播亚冠恒大决赛，就好像是在嚷：“郑智舅舅，郜林有了！”——...
    曾经而已阅读 221评论 0赞 1