MYSQL实现行转列的三种方式

题目

​ 给定 student_score 表，内容如下：

select * from student_score;

nane  subject  score
-------------------------------
张三	  语文	78
张三	  数学	88
张三	  英语	98
李四	  语文	89
李四	  数学	76
李四	  英语	90
王五	  语文	99
王五	  数学	66
王五	  英语	91

​ 要求用一条 sql 语句查出如下结果：

姓名   语文  数学  英语
张三    78    88    98
李四    89    76    90
王五    89    56    89

解题

​ 这是一道典型的行转列题目，给定表中行有学科，而目标表中列是学科。

解法一：case when

​ 通过case when，可以较容易的实现将行转换成列的功能，这是通用的思路。

SELECT NAME,
	max( CASE SUBJECT WHEN '语文' THEN score END ) '语文',
	max( CASE SUBJECT WHEN '数学' THEN score END ) '数学',
	max( CASE SUBJECT WHEN '英语' THEN score END ) '英语' 
FROM
	student_score 
GROUP BY
	NAME

解法二：join

​ 解法二和解法三在部分情况下是可行的，用来拓展思路。

--1. 先查询出每个学科表数据，即获取一列的数据
select name,score 'y' from student_score where subject = '语文';
select name,score 's' from student_score where subject = '数学';
select name,score 'e' from student_score where subject = '英语';
/*
查询结果类似如下：
name  y
张三	78
李四	89
王五	99
*/

--2. 将第1步查出的三张表进行连接，得出最终结果
SELECT
	ss1.NAME,
	ss1.y '语文',
	ss2.s '数学',
	ss3.e '英语' 
FROM
	( SELECT NAME, score 'y' FROM student_score WHERE SUBJECT = '语文' ) ss1
	JOIN ( SELECT NAME, score 's' FROM student_score WHERE SUBJECT = '数学' ) ss2 ON ss1.NAME = ss2.NAME 
	JOIN ( SELECT NAME, score 'e' FROM student_score WHERE SUBJECT = '英语' ) ss3 ON ss1.NAME = ss3.NAME;

解法三：union all

--1. 第1步和 join 方式一样，查询出每个学科表数据
select name,score 'y' from student_score where subject = '语文';
select name,score 's' from student_score where subject = '数学';
select name,score 'e' from student_score where subject = '英语';
/*
查询结果类似如下：
name  y
张三	78
李四	89
王五	99
*/

--2. 通过 union all 将三个表合并起来，并通过 name 进行分组，合并成最终结果
SELECT
	s.NAME,
	max( s.y ) '语文',
	max( s.s ) '数学',
	max( s.e ) '英语' 
FROM
	(	SELECT NAME, score y, 0 s, 0 e FROM	student_score WHERE	SUBJECT = '语文' 
	UNION ALL
		select name,0 y,score s, 0 e from student_score where subject = '数学' 
	UNION ALL
		select name,0 y, 0 s, score e from student_score where subject = '英语' ) s
GROUP BY s.name ;