前置知识
-
CASE 流程控制语句：CASE 语句为多分支语句结构，该语句首先从 WHEN 后的 VALUE 中查找与 CASE 后的 VALUE 相等的值，如果查找到则执行该分支的内容，否则执行 ELSE 后的内容
-
CASE value WHEN value THEN ... WHEN value THEN ... ELSE ... END CASE -- 或者 CASE WHEN value THEN ... WHEN value THEN ... ELSE ... END CASE
-
- UNION 与 UNION ALL
- 对重复结果集的处理：UNION 会去掉重复记录，UNION ALL 不会
- 对排序的处理：UNION 会排序，UNION ALL 只是简单地将两个结果集合并
- 效率方面的区别：因为 UNION 做去重和排序处理，因此效率比 UNION ALL 慢很多
简介
行转列即将多行原本同一列下多行的不同内容作为多个字段进行数据的展示问题，而列转行则反之
行转列
数据准备
以学生的分数表作为例子，将学生的分数按照同一个学生的分数都展示在一行中并计算该生的总分数
CREATE TABLE `score` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`studentId` varchar(20) NOT NULL COMMENT '学号',
`course` varchar(20) DEFAULT NULL COMMENT '课程名',
`score` double DEFAULT NULL COMMENT '成绩',
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=utf8;
INSERT INTO score(id, studentId, course, score) VALUES (1, '2101', '语文', 88);
INSERT INTO score(id, studentId, course, score) VALUES (2, '2101', '数学', 95);
INSERT INTO score(id, studentId, course, score) VALUES (3, '2101', '英语', 70);
INSERT INTO score(id, studentId, course, score) VALUES (4, '2102', '语文', 88);
INSERT INTO score(id, studentId, course, score) VALUES (5, '2102', '数学', 92);
INSERT INTO score(id, studentId, course, score) VALUES (6, '2102', '英语', 85);
INSERT INTO score(id, studentId, course, score) VALUES (7, '2103', '语文', 70);
INSERT INTO score(id, studentId, course, score) VALUES (8, '2103', '数学', 95);
INSERT INTO score(id, studentId, course, score) VALUES (9, '2103', '英语', 90);结果样例如下
这里行转列是将原来的 course 字段的多行内容选出来，作为结果集中的不同列，并根据 studentId 进行分组显示对应的 score
实现方式
- 使用 CASE……WHEN……THEN实现
-
SELECT studentId as '学号', SUM( CASE course WHEN '语文' THEN score ELSE 0 END ) AS '语文', SUM( CASE course WHEN '数学' THEN score ELSE 0 END ) AS '数学', SUM( CASE course WHEN '英语' THEN score ELSE 0 END ) AS '英语', SUM( score ) AS '总分' FROM score GROUP BY studentId - 注意，SUM() 是为了能够使用 GROUP BY 根据 studentId 进行分组，因为每一个 studentId 对应的 course="语文" 的记录只有一条，所以 SUM() 的值就等于对应那一条记录的 score 的值。假如 studentId='2101' and course='语文' 的记录有两条，则此时 SUM() 的值将会是这两条记录的和，同理，使用 Max() 的值将会是这两条记录里面值最大的一个。但是正常情况下，一个学生对应一个课程只有一个分数，可以使用SUM()、MAX()、MIN()、AVG()等聚合函数都可以达到行转列的效果
-
- 使用 IF() 函数实现
-
SELECT studentId AS '学号', SUM( IF ( course = '语文', score, 0 ) ) AS '语文', SUM( IF ( course = '数学', score, 0 ) ) AS '数学', SUM( IF ( course = '英语', score, 0 ) ) AS '英语', SUM( score ) AS '总分' FROM score GROUP BY studentId -
注意，IF(course='语文',score,0) 作为条件，即对所有 course='语文' 的记录的 score 字段进行 SUM()、MAX()、MIN()、AVG() 操作，如果 score 没有值则默认为0
-
- SUM( IF( ) ) 生成列 和 UNION 生成科目总成绩汇总行
-
SELECT studentId, SUM( IF ( course = '语文', score, 0 ) ) AS 语文, SUM( IF ( course = '数学', score, 0 ) ) AS 数学, SUM( IF ( course = '英语', score, 0 ) ) AS 英语, SUM( score ) AS '总分' FROM score GROUP BY studentId UNION SELECT '总分', SUM( IF ( course = '语文', score, 0 ) ) AS 语文, SUM( IF ( course = '数学', score, 0 ) ) AS 数学, SUM( IF ( course = '英语', score, 0 ) ) AS 英语, SUM( score ) FROM score
-
列转行
数据准备
以学生的分数表作为例子，将学生的分数按照同一个学生不同科目的分数都展示在一行中
CREATE TABLE score (
id INT ( 11 ) NOT NULL auto_increment,
studnetId VARCHAR ( 20 ) NOT NULL COMMENT '学号',
chineseScore DOUBLE COMMENT '语文成绩',
mathScore DOUBLE COMMENT '数学成绩',
englishScore DOUBLE COMMENT '英语成绩',
PRIMARY KEY ( id )
) ENGINE = INNODB DEFAULT CHARSET = utf8;
INSERT INTO score(studnetId,chineseScore,mathScore,englishScore) VALUES ('2101',88,95,70);
INSERT INTO score(studnetId,chineseScore,mathScore,englishScore) VALUES ('2102',88,92,85);
INSERT INTO score(studnetId,chineseScore,mathScore,englishScore) VALUES ('2103',70,95,90);结果样例如下
这里的列转行即将 studentId 的每个科目分数分散成一条记录显示出来
实现方式
- 通过 UNION ALL 将学生的单个科目成绩结果汇集起来
-
SELECT studentId,'语文' AS course,chineseScore AS score FROM score UNION ALL SELECT studentId,'数学' AS course,mathScore AS score FROM score UNION ALL SELECT studentId,'英语' AS course,englishScore AS score FROM score ORDER BY studentId
-