MySQL--行转列 和 列转行

前置知识

• 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