SQL学习六-CASE WHEN

SQL学习六-CASE WHEN

有点类似于编程语言中的if else

数据准备

今天使用的数据是前面几节中使用到的数据

ID	NAME	CLASSID
1	xiaoming	1
2	xiaowang	1
3	xiaotian	2
4	xiaoming	2
5	xiaowang	3
6	xiaotian	4
7	xiaogang	1
8	xiaoqiang	1
9	xiaojing	2
10	xiaotie	2
11	wangqiang	3
12	wangwei	3
13	liming	4
14	liqiang	4

插入语句如下：

INSERT INTO Student
VALUES 
(1, 'xiaoming', 1),
 (2, 'xiaowang', 1),
 (3, 'xiaotian', 2),
 (4, 'xiaoming', 2),
 (5, 'xiaowang', 3),
 (6, 'xiaotian', 4),
(7, 'xiaogang', 1),
(8, 'xiaoqiang', 1),
(9, 'xiaojing', 2),
(10, 'xiaotie', 2),
(11, 'wangqiang', 3),
(12, 'wangwei', 3),
(13, 'liming', 4),
(14, 'liqiang', 4);

CASE WHEN

CASE WHEN 类比于编程语言里面的if else
例如java中的if else if，可以写成下面的样子

if (ID==1) {
	输出2;
} else if (ID==2) {
	输出1;
} else {
	输出10;
}

类比java语言，CASE WHEN可以写作

CASE WHEN ID = 1
	THEN 2
WHEN ID = 2
	THEN 1
ELSE 10
	END

那这个语句具体在什么场景下用呢?
例如：

以现有数据为例，搞定下面的问题：
输出以下内容：将一班的学生转到二班，将二班的学生转到一班，三班的学生不变

SELECT ID, NAME, CLASSID,
(CASE WHEN CLASSID=1 THEN 2
WHEN CLASSID=2 THEN 1
ELSE CLASSID END) AS A
FROM student;

结果如下，可以对比实际数据看是否更换

如果有的列没有考虑到，就会填充NULL，例如上面的3和4，如果去掉了ELSE，就是NULL

SELECT ID, NAME, CLASSID,
(CASE WHEN CLASSID=1 THEN 2
WHEN CLASSID=2 THEN 1 END) AS A
FROM student;

CASE WHEN 和聚集函数的组合使用

案例： 有如下表格

NAME	CLASS	GRAGE
A	C1	1
B	C1	2
C	C1	3
A	C2	1
B	C2	2
C	C2	3
A	C3	1
B	C3	2
C	C3	3
A	C4	1
B	C4	2
C	C4	3

建表语句和插入语句如下：

CREATE TABLE IF NOT EXISTS GRAGETABLE (
	NAME VARCHAR(20),
	CLASS VARCHAR(20),
	GRADE INT
);

INSERT INTO GRAGETABLE 
VALUES 
('A','C1',1),
('B','C1',2),
('C','C1',3),
('A','C2',1),
('B','C2',2),
('C','C2',3),
('A','C3',1),
('B','C3',2),
('C','C3',3),
('A','C4',1),
('B','C4',2),
('C','C4',3);

期望得到下面的表格

NAME	C1	C2	C3	C4
A	1	1	1	1
B	2	2	2	2
C	3	3	3	3

如何实现呢？

首先，该表格的实现考虑如何将上述表格放平，即将C1到C4课程平放，此时要求属性名为课程名，属性值为课程的分数，如何将课程和分数联系起来，可以考虑前面学到的CASE WHEN，例如if (课程 === ‘xxx’) THEN 输出成绩。这个操作可以将课程作为属性，然后将成绩作为属性值放起来。

( CASE WHEN CLASS='C1' THEN GRADE ) AS C1;

然后因为结果是每一门课程的分数，故必然要进行分组。

之后针对某个课程要得到当前的分数，如果不是当前的课程，那么就不要分数，这又应该怎么实现呢？有一个思路，可以给原始的表增加一列，表示当前的分数，但是该列只保存对应的某一个课程的分数，例如，增加C1对应的一列，该列只有C1的分数，C2~C4的分数都是-1 或者 NULL

过程的表

NAME	CLASS	GRAGE	C1	C2	C3	C4
A	C1	1	1	-1	-1	-1
B	C1	2	2	-1	-1	-1
C	C1	3	3	-1	-1	-1
A	C2	1	-1	1	-1	-1
B	C2	2	-1	2	-1	-1
C	C2	3	-1	3	-1	-1
A	C3	1	-1	-1	1	-1
B	C3	2	-1	-1	2	-1
C	C3	3	-1	-1	3	-1
A	C4	1	-1	-1	-1	1
B	C4	2	-1	-1	-1	2
C	C4	3	-1	-1	-1	3

如果能够得到这个表，并且针对每个组进行了分组，例如对于C1列，ABC的最大值分别是1，2，3，正好对应了ABC C1课程的得分，故此时可将SQL写成

MAX( CASE WHEN CLASS='C1' THEN GRADE ELSE -1 END)  AS C1
MAX( CASE WHEN CLASS='C2' THEN GRADE ELSE -1 END)  AS C2
MAX( CASE WHEN CLASS='C3' THEN GRADE ELSE -1 END)  AS C3
MAX( CASE WHEN CLASS='C4' THEN GRADE ELSE -1 END)  AS C4

此时已经得到了所有的列，实现的SQL可以写成

SELECT NAME,
MAX( CASE WHEN CLASS='C1' THEN GRADE ELSE -1 END)  AS C1,
MAX( CASE WHEN CLASS='C2' THEN GRADE ELSE -1 END)  AS C2,
MAX( CASE WHEN CLASS='C3' THEN GRADE ELSE -1 END)  AS C3,
MAX( CASE WHEN CLASS='C4' THEN GRADE ELSE -1 END)  AS C4
FROM GRAGETABLE 
GROUP BY NAME;

结果如下：

当前节使用到的SQL语句在附件中