Sql case函数

case函数

case函数分为普通case函数和搜索case函数，区别是普通的case函数写法相对简洁,但是功能也相对简单，搜索case函数的功能更加强大。

1. 普通case函数

CASE  <表达式>
   WHEN <值1> THEN <操作>
   WHEN <值2> THEN <操作>
   ...
   ELSE <操作>
END 
1
2
3
4
5
6

2. 搜索case函数

CASE
    WHEN <条件1> THEN <命令>
    WHEN <条件2> THEN <命令>
    ...
    ELSE commands
END
1
2
3
4
5
6

case函数常用作 行转列（重点）、检查表中字段值是否一致、结合分组统计数据、分条件更新字段值。

一、行转列

1. 建表sql

CREATE TABLE RowtoCol (
	s_name VARCHAR ( 10 ),
	s_course VARCHAR ( 10 ),
	s_score INT 
)

INSERT INTO RowtoCol VALUES ('张三','语文',73);
INSERT INTO RowtoCol VALUES ('张三','数学',86);
INSERT INTO RowtoCol VALUES ('张三','物理',99);
INSERT INTO RowtoCol VALUES ('李四','语文',78);
INSERT INTO RowtoCol VALUES ('李四','数学',98);
INSERT INTO RowtoCol VALUES ('李四','物理',93);

SELECT * FROM RowtoCol;*
1
2
3
4
5
6
7
8
9
10
11
12
13
14

2. 普通case函数实现行转列

SELECT
	RowtoCol.s_name,
	MAX(CASE s_course WHEN '语文' THEN s_score ELSE 0 END) 语文,
	MAX(CASE s_course WHEN '数学' THEN s_score ELSE 0 END) 数学,
	MAX(CASE s_course WHEN '物理' THEN s_score ELSE 0 END) 物理 
FROM
	RowtoCol 
GROUP BY
	RowtoCol.s_name
1
2
3
4
5
6
7
8
9

3. 搜索case函数行转列

SELECT
	RowtoCol.s_name,
	MAX(CASE WHEN s_course='语文' THEN s_score ELSE 0 END ) 语文,
	MAX(CASE WHEN s_course='数学' THEN s_score ELSE 0 END ) 数学,
	MAX(CASE WHEN s_course='物理' THEN s_score ELSE 0 END ) 物理
FROM
	RowtoCol 
GROUP BY
	RowtoCol.s_name
1
2
3
4
5
6
7
8
9

二、检查表中字段值是否一致

1. 建表sql

CREATE TABLE user1 (
	name VARCHAR ( 10 ),
	info VARCHAR ( 20 )
)

INSERT INTO user1 VALUES ('张三','I\'m fan ');
INSERT INTO user1 VALUES ('李四','Thank you');
INSERT INTO user1 VALUES ('王二','And you');

select * from user1;


CREATE TABLE user2 (
	name VARCHAR ( 10 ),
	info VARCHAR ( 20 )
)

INSERT INTO user2 VALUES ('张三','I\'m fan ');
INSERT INTO user2 VALUES ('李四','Thanks');
INSERT INTO user2 VALUES ('王二','And you');

select * from user2;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22

2. 搜索case函数
这里只能使用搜索case函数不能使用普通case函数，因为普通case函数when之后只能是等于判断。

SELECT
	info,
	( CASE WHEN user1.info IN ( SELECT info FROM user2 ) THEN '数据一致' ELSE '数据不一致' END ) AS '比较结果'
FROM
	user1
1
2
3
4
5

三、结合分组统计数据

CREATE TABLE Country (
	id int,
	city VARCHAR ( 10 ),
	population int
)

INSERT INTO Country VALUES (1, '北京',500);
INSERT INTO Country VALUES (2, '哈尔滨',80);
INSERT INTO Country VALUES (3, '天津',100);
INSERT INTO Country VALUES (4, '上海',350);
INSERT INTO Country VALUES (5, '南京',100);
INSERT INTO Country VALUES (6, '广州',400);

select * from Country
1
2
3
4
5
6
7
8
9
10
11
12
13
14

2. 普通case函数

SELECT
	(
	CASE
			city 
			WHEN '北京' THEN '北方' 
			WHEN '哈尔滨' THEN '北方' 
			WHEN '天津' THEN '北方' 
			WHEN '上海' THEN '南方' 
			WHEN '南京' THEN '南方' 
			WHEN '广州' THEN '南方' 
			ELSE '其它' 
		END 
		) AS 区域,
		sum( population ) AS '人口数（百万）'
	FROM
		Country 
	GROUP BY
		(
		CASE
				city 
				WHEN '北京' THEN '北方' 
				WHEN '哈尔滨' THEN '北方' 
				WHEN '天津' THEN '北方' 
				WHEN '上海' THEN '南方' 
				WHEN '南京' THEN '南方' 
				WHEN '广州' THEN'南方' 
				ELSE '其它' 
		END 
	)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29

3. 搜索case函数

SELECT
	(
	CASE 
			WHEN city in ('北京', '哈尔滨', '天津') THEN '北方' 
			WHEN city in ('上海', '南京', '广州') THEN '南方' 
			ELSE '其它' 
		END 
		) AS 区域,
		sum( population ) AS '人口数（百万）'
	FROM
		Country 
	GROUP BY
		(
		CASE 
			WHEN city in ('北京', '哈尔滨', '天津') THEN '北方' 
			WHEN city in ('上海', '南京', '广州') THEN '南方' 
			ELSE '其它' 
		END 
	)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19

四、分条件更新字段值

需求: 将工资低于4000的员工涨幅工资30%,工资等于高于5000的员工涨幅10%,数据如下:

CREATE TABLE Employee (
	name VARCHAR ( 10 ),
	salary int
)

INSERT INTO Employee VALUES ('张三',6000);
INSERT INTO Employee VALUES ('李四',5000);
INSERT INTO Employee VALUES ('王二',3900);

select * from Employee
1
2
3
4
5
6
7
8
9
10

一开始可能会想直接根据salary更新数据

update Employee Set salary = salary * (1 + 0.3) where salary < 4000;
update Employee Set salary = salary * (1 + 0.1) where salary >= 5000;
1
2

但是,如果是这样执行的话实际上会存在问题,比如:原来工资在4000的员工,执行完第一条语句后工资会变成5070,此时,再执行第二条更新语句,因为满足工资大于等于5000,则又会去添加多10%的工资。
可以使用搜索case函数解决这种情况

update Employee Set salary =
	(
	CASE
			WHEN salary < 4000 THEN salary * ( 1 + 0.3 )
			WHEN salary >= 5000 THEN salary * ( 1 + 0.1 ) 
			ELSE salary 
		END 
	) 
1
2
3
4
5
6
7
8

五、普通case函数和搜索case函数和区别

普通case函数和搜索case函数的区别是普通的case函数写法相对简洁,但是功能也相对简单，搜索case函数的功能更加强大，具体描述如下：
1、简单case函数判断条件只能是等于,而搜索case函数的条件可以是子查询,In,大于、等于等等。
2、如果只是使用简单的条件分组,可以选择普通case函数,如果需要判断更多的场景,则选择搜索case更好。