Mysql实现表格的行列转换

Mysql在面试和实际使用过程中会经常用到行列转换的场景，具体转换效果如下：


以下通过学生成绩表来展示具体转换过程。
1、创建学生成绩表

create table sg_grade(
	stu varchar(20),
	Chinese varchar(20),
	Math varchar(20),
	English varchar(20),
	Politics varchar(20),
	History varchar(20),
	Physics varchar(20),
	Chemistry varchar(20),
	primary key(stu)
)
insert into sg_grade values
("james",33,44,55,66,77,88,99),
("abby",23,34,45,56,67,78,89),
("vivian",44,55,66,77,88,99,33),
("nancy",98,87,76,54,43,32,21);

2、多列转行(创建视图sg_zi)

-- 多列转行（逆透视）
create view sg_zi as 
select stu,'Chinese' course,Chinese score from sg_grade
union all
select stu,'Math' course,Math score from sg_grade
union all
select stu,'English' course,English score from sg_grade	
union all
select stu,'Politics' course,Politics score from sg_grade
union all
select stu,'History' course,History score from sg_grade
union all
select stu,'Physics' course,Physics score from sg_grade
union all
select stu,'Chemistry' course,Chemistry score from sg_grade
order by stu;

3、多行转列

-- ①case when转换
select stu,
	sum(case course when 'Chinese' then score else null end) as Chinese,
	sum(case course when 'Math' then score else null end) as Math,
	sum(case course when 'English' then score else null end) as English,
	sum(case course when 'Politics' then score else null end) as Politics,
	sum(case course when 'History' then score else null end) as History,
	sum(case course when 'Physics' then score else null end) as Physics,
	sum(case course when 'Chemistry' then score else null end) as Chemistry
from sg_zi group by stu;
-- ②if函数转换
select stu,
	sum(if(course='Chinese',score,null)) as Chinese,
	sum(if(course='Math',score,null)) as Math,
	sum(if(course='English',score,null)) as English,
	sum(if(course='Politics',score,null)) as Politics,
	sum(if(course='History',score,null)) as History,
	sum(if(course='Physics',score,null)) as Physics,
	sum(if(course='Chemistry',score,null)) as Chemistry
from sg_zi group by stu;