简单CASE WHEN函数：

CASE SCORE WHEN 'A' THEN '优' ELSE '不及格' END
CASE SCORE WHEN 'B' THEN '良' ELSE '不及格' END
CASE SCORE WHEN 'C' THEN '中' ELSE '不及格' END

 等同于，使用CASE WHEN条件表达式函数实现：

CASE WHEN SCORE = 'A' THEN '优'
     WHEN SCORE = 'B' THEN '良'
     WHEN SCORE = 'C' THEN '中' ELSE '不及格' END

 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 result

END

condition是一个返回布尔类型的表达式，如果表达式返回true，则整个函数返回相应result的值，如果表达式皆为false，则返回ElSE后result的值，如果省略了ELSE子句，则返回NULL。

下面介绍几种常用场景。


场景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中使用子查询

根据城市用电量多少，计算用电成本。假设电能耗单价分为三档，根据不同的能耗值，使用相应价格计算成本。


 价格表如下：

P_PRICE
			
P_LEVEL
			
P_LIMIT
		
1.20
			
0
			
10
		
1.70
			
1
			
30
		
2.50
			
2
			
50
		
当能耗值小于10时，使用P_LEVEL=0时的P_PRICE的值，能耗值大于10小于30使用P_LEVEL=1时的P_PRICE的值...

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)


 场景5：结合max聚合函数






CASE WHEN 函数使用起来简单易懂，此篇文章只作了简单的使用介绍，还需在实际工作中根据业务场景不同来灵活使用。

 

MySQL 的 case when 的语法有两种：
简单函数
CASE [col_name] WHEN [value1] THEN [result1]…ELSE [default] END
搜索函数
CASE WHEN [expr] THEN [result1]…ELSE [default] END
这两种语法有什么区别呢？
简单函数
CASE [col_name] WHEN [value1] THEN [result1]…ELSE [default] END： 枚举这个字段所有可能的值*
SELECT
	NAME '英雄',
	CASE NAME
		WHEN '德莱文' THEN
			'斧子'
		WHEN '德玛西亚-盖伦' THEN
			'大宝剑'
		WHEN '暗夜猎手-VN' THEN
			'弩'
		ELSE
			'无'
	END '装备'
FROM
	user_info;


搜索函数
CASE WHEN [expr] THEN [result1]…ELSE [default] END：搜索函数可以写判断，并且搜索函数只会返回第一个符合条件的值，其他case被忽略
# when 表达式中可以使用 and 连接条件
SELECT
	NAME '英雄',
	age '年龄',
	CASE
		WHEN age < 18 THEN
			'少年'
		WHEN age < 30 THEN
			'青年'
		WHEN age >= 30
		AND age < 50 THEN
			'中年'
		ELSE
			'老年'
	END '状态'
FROM
	user_info;


聚合函数 sum 配合 case when 的简单函数实现多表 left join 的行转列
注：曾经有个爱学习的路人问我，“那个sum()只是为了好看一点吗？”，left join会以左表为主，连接右表时，得到所有匹配的数据，再group by时只会保留一行数据，因此case when时要借助sum函数，保留其他列的和。如果你还是不明白的话，那就亲手实践一下，只保留left join看一下结果，再group by，看一下结果。例如下面的案例：学生表/课程表/成绩表 ，三个表left join查询每个学生所有科目的成绩，使每个学生及其各科成绩一行展示。
SELECT
	st.stu_id '学号',
	st.stu_name '姓名',
	sum(
		CASE co.course_name
		WHEN '大学语文' THEN
			sc.scores
		ELSE
			0
		END
	) '大学语文',
	sum(
		CASE co.course_name
		WHEN '新视野英语' THEN
			sc.scores
		ELSE
			0
		END
	) '新视野英语',
	sum(
		CASE co.course_name
		WHEN '离散数学' THEN
			sc.scores
		ELSE
			0
		END
	) '离散数学',
	sum(
		CASE co.course_name
		WHEN '概率论与数理统计' THEN
			sc.scores
		ELSE
			0
		END
	) '概率论与数理统计',
	sum(
		CASE co.course_name
		WHEN '线性代数' THEN
			sc.scores
		ELSE
			0
		END
	) '线性代数',
	sum(
		CASE co.course_name
		WHEN '高等数学' THEN
			sc.scores
		ELSE
			0
		END
	) '高等数学'
FROM
	edu_student st
LEFT JOIN edu_score sc ON st.stu_id = sc.stu_id
LEFT JOIN edu_courses co ON co.course_no = sc.course_no
GROUP BY
	st.stu_id
ORDER BY
	NULL;


行转列测试数据
-- 创建表  学生表
CREATE TABLE `edu_student` (
	`stu_id` VARCHAR (16) NOT NULL COMMENT '学号',
	`stu_name` VARCHAR (20) NOT NULL COMMENT '学生姓名',
	PRIMARY KEY (`stu_id`)
) COMMENT = '学生表' ENGINE = INNODB;

-- 课程表 
CREATE TABLE `edu_courses` (
	`course_no` VARCHAR (20) NOT NULL COMMENT '课程编号',
	`course_name` VARCHAR (100) NOT NULL COMMENT '课程名称',
	PRIMARY KEY (`course_no`)
) COMMENT = '课程表' ENGINE = INNODB;

-- 成绩表
CREATE TABLE `edu_score` (
	`stu_id` VARCHAR (16) NOT NULL COMMENT '学号',
	`course_no` VARCHAR (20) NOT NULL COMMENT '课程编号',
	`scores` FLOAT NULL DEFAULT NULL COMMENT '得分',
	PRIMARY KEY (`stu_id`, `course_no`)
) COMMENT = '成绩表' ENGINE = INNODB;

-- 插入数据

-- 学生表数据

INSERT INTO edu_student (stu_id, stu_name)
VALUES
	('1001', '盲僧'),
	('1002', '赵信'),
	('1003', '皇子'),
	('1004', '寒冰'),
	('1005', '蛮王'),
	('1006', '狐狸');

-- 课程表数据 
INSERT INTO edu_courses (course_no, course_name)
VALUES
	('C001', '大学语文'),
	('C002', '新视野英语'),
	('C003', '离散数学'),
	(
		'C004',
		'概率论与数理统计'
	),
	('C005', '线性代数'),
	('C006', '高等数学');

-- 成绩表数据
INSERT INTO edu_score (stu_id, course_no, scores)
VALUES
	('1001', 'C001', 67),	('1002', 'C001', 68),	('1003', 'C001', 69),	('1004', 'C001', 70),	('1005', 'C001', 71),
	('1006', 'C001', 72),	('1001', 'C002', 87),	('1002', 'C002', 88),	('1003', 'C002', 89),	('1004', 'C002', 90),
	('1005', 'C002', 91),	('1006', 'C002', 92),	('1001', 'C003', 83),	('1002', 'C003', 84),	('1003', 'C003', 85),
	('1004', 'C003', 86),	('1005', 'C003', 87),	('1006', 'C003', 88),	('1001', 'C004', 88),	('1002', 'C004', 89),
	('1003', 'C004', 90),	('1004', 'C004', 91),	('1005', 'C004', 92),	('1006', 'C004', 93),	('1001', 'C005', 77),
	('1002', 'C005', 78),	('1003', 'C005', 79);

case when练习
有如下表结构，统计2019-10-21 00:00:00~2019-12-02 23:59:59时间段内的用户并标记新老用户
CREATE TABLE `tb_hotel_user`  (
  `customer_id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '旅客id',
  `name` varchar(200) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '姓名',
  `user_id` bigint(10) NULL DEFAULT NULL COMMENT '用户id',
  `check_in_time` datetime(0) NULL DEFAULT NULL COMMENT '入住时间',
  `check_out_time` datetime(0) NULL DEFAULT NULL COMMENT '离店时间',
  PRIMARY KEY (`customer_id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 6 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Compact;

INSERT INTO `tb_hotel_user` VALUES (1, '张三', 1, '2019-12-02 14:18:57', NULL);
INSERT INTO `tb_hotel_user` VALUES (2, '刘大', 2, '2019-11-08 14:19:07', NULL);
INSERT INTO `tb_hotel_user` VALUES (3, '关二', 3, '2019-10-17 14:19:21', NULL);
INSERT INTO `tb_hotel_user` VALUES (4, '关二', 3, '2019-12-02 14:19:44', NULL);
INSERT INTO `tb_hotel_user` VALUES (5, '赵四', 4, '2019-11-29 14:19:54', NULL);


-- 答案
SELECT
	a.user_id,
CASE
	
	WHEN ISNULL( b.user_id ) THEN
	1 ELSE 2 
	END newUser 
FROM
	( SELECT DISTINCT user_id FROM tb_hotel_user WHERE check_in_time >= '2019-10-21 00:00:00' AND check_in_time <= '2019-12-02 23:59:59' ) a
LEFT JOIN ( SELECT user_id FROM tb_hotel_user WHERE check_in_time <= '2019-12-02 23:59:59' GROUP BY user_id HAVING count( * ) > 1 ) b ON a.user_id = b.user_id

前几天，为了给产品分析当前用户数据结构，写sql的时候使用到了case when，今天来总结一下case when 的使用方法，以此为戒，感觉写的不好请拍砖，感觉写的还可以，给哥们点个赞，或者回复一下，让我意识到我不是一个人在战斗，好了废话不多说了，进入正题。

关于case when的使用情况，我总结下来有三种，第一、等值转换，第二、范围转换，第三、列转行操作。

等值转换

咱们在设计数据库的时候总是会把用户的性别用int存储（'0'为女，'1'为男），但是怎么把它转换成汉字显示呢？

原始表数据



SQL语句

select

name as '名字',

(case sex when 0 then '女' else '男' end) as '性别'

from test.student;

ps.最后的 'end' 别丢了，我刚开始使用的时候我就有这个毛病，并且一般的case when语句都会比较长，最好添加小括号包起来，这样更容易阅读。

查询结果



范围转换

有的时候，也会遇到这种情况，按照用户成绩显示优（90+）、良（80-90）、及格（60-80）、未及格（60-），这个跟第一个不同的是，他是一个分数的范围，要怎么转换成汉子显示呢？你可能觉得很简单，不就是吧when那换成条件吗？先打住咱们往下看

原始表数据



 

SQL语句

select 

name as '姓名'

,(case score when score>=90 then '优' when score>=80 then '良' when score>=60 then '及格' else '不及格' end) as '等级'

from test.stu_score; 

这样写对不？

。

。

。

这个查出来的结果是



 



这是为啥呢？想明白了吗？ 

因为 case when就像一个 switch case语句一样，如果你在case后填了东西，它会拿后它跟when 对比，咱们的 写的case 后写=了 score 而when后面写了score>=90 ，然而，'score' 等于 'score>=90'吗? 显然不等于，那该怎么写呢？

select 

name as '姓名'

,(case score when score>=90 then '优' when score>=80 then '良' when score>=60 then '及格' else '不及格' end) as '等级'

from test.stu_score; 



 

列转行操作

还是用学生的例子吧，现在有图1学生成绩数据， 现在要怎么按图2显示出来呢？

图1



图2



 

第一步 先按照科目分开， 符合条件的设置分数，不符合的给置零。

select name as '姓名'

,(case course when '语文' then score else 0 end) as '语文'

,(case course when '数学' then score else 0 end) as '数学'

,(case course when '英语' then score else 0 end) as '英语'

from test.course_score



 

然后再按照名字group by ，对分数求max。

select name as '姓名'

,max(case course when '语文' then score else 0 end) as '语文'

,max(case course when '数学' then score else 0 end) as '数学'

,max(case course when '英语' then score else 0 end) as '英语'

from test.course_score group by name;



 

后天就是除夕了，在这里给各位CSDN的朋友拜个早年，祝各位在新一年里，工资翻倍，不掉头发！

老铁们，别光收藏啊，点个赞再走啊！

❤️ 看完三件事

如果你觉得这篇内容对你挺有启发，我想邀请你帮我三个小忙：

点赞，让更多的人也能看到这篇内容（收藏不点赞，都是耍流氓 -_-）
	
微信搜索jinhuazhe2010加我，进组织，不定期分享干货。
	
也看看其它文章

目录

 

注意事项

写法一

写法二

 

注意事项


case when then 多条件 注意点：

1、语法 case when 条件1（布尔表达式）then 结果1 when 条件2（布尔表达式） then 结果2  else 结果xx end

2、上面的标颜色的要注意，else子句表示都不符合时就填结果xx，else子句是可选的，可以不写。

3、case when 必须以end结尾！勿忘！  


写法一

-- 勿忘end！                                                                                                    
select substr(key,13,2),
case when birthday<'1968' then '50up'
 when birthday<'1978' then '40-50'
 when birthday<'1983' then '35-40'
 when birthday<'1988' then '30-35'
 when birthday<'1992' then '26-30'
 when birthday<'1998' then '20-25'
 else 'other' 
end,
count(1) 
from hiveTable 
group by substr(key,13,2), 
case when birthday<'1968' then '50up'
 when birthday<'1978' then '40-50'
 when birthday<'1983' then '35-40'
 when birthday<'1988' then '30-35'
 when birthday<'1992' then '26-30'
 when birthday<'1998' then '20-25'
 else 'other' 
end
-- 勿忘end！

写法二

-- 这种写法某些数据库不支持
SELECT
    SUM(c.population) AS '人口',
    CASE c.country
        WHEN '中国'   THEN '亚洲'
        WHEN '韩国'   THEN '亚洲'
        WHEN '美国'   THEN '北美洲'
        WHEN '加拿大' THEN '北美洲'
 ELSE '其他' END AS '洲'
FROM
    country c
GROUP BY CASE c.country
        WHEN '中国'   THEN '亚洲'
        WHEN '韩国'   THEN '亚洲'
        WHEN '美国'   THEN '北美洲'
        WHEN '加拿大' THEN '北美洲'
 ELSE '其他' END;

总结

case when 必须以end结尾！勿忘！

建议使用第一种方式，虽然看似繁琐，但便于理解且不易出错。