MySql行转列&列转行方式

create table test1(
id int  auto_increment primary key ,
name varchar(255),
course varchar(255),
score int
)

insert into test1(name,course,score) values ('张三','语文',120);
insert into test1(name,course,score) values ('张三','数学',100);
insert into test1(name,course,score) values ('张三','英语',82);
insert into test1(name,course,score) values ('李四','语文',89);
insert into test1(name,course,score) values ('李四','数学',99);
insert into test1(name,course,score) values ('李四','英语',87);
insert into test1(name,course,score) values ('王五','语文',78);
insert into test1(name,course,score) values ('王五','数学',85);
insert into test1(name,course,score) values ('王五','英语',145);
insert into test1(name,course,score) values ('王五','物理',40);
insert into test1(name,course,score) values ('王五','化学',62);

-- 使用case when then else，这里使用sum函数也可以
select name,
max(case course when '语文' then score else 0 end)as 'chinese',
max(case course when '数学' then score else 0 end)as 'math',
max(case course when '英语' then score else 0 end)as 'english',
max(case course when '物理' then score else 0 end)as 'wuli',
max(case course when '化学' then score else 0 end)as 'huaxue'
from test1 group by name

-- 使用if语句，这里使用sum函数也可以
select name,
max(if(course = '语文',score,0))as 'chinese',
max(if(course = '数学',score,0))as 'math',
max(if(course = '英语',score,0))as 'english',
max(if(course = '物理',score,0))as 'wuli',
max(if(course = '化学',score,0))as 'huaxue'
from test1 group by name

-- 动态拼接sql语句，不管多少行都会转列
set @sql = null;
select group_concat(distinct concat('max(if(a.course = ''',a.course,''', a.score, 0)) as ''',a.course, '''')) into @sql from test1 a;
set @sql = concat('select name,', @sql, 'from test1 a group by a.name' );
prepare stmt from @sql;  -- 动态生成脚本，预备一个语句
execute stmt;            -- 动态执行脚本，执行预备的语句
deallocate prepare stmt; -- 释放预备的语句 

-- 通过这个查询拼接的sql
select @sql

-- 查询出来的结果
select name,max(if(a.course = '语文', a.score, 0)) as '语文',max(if(a.course = '数学', a.score, 0)) as '数学',max(if(a.course = '英语', a.score, 0)) as '英语',max(if(a.course = '物理', a.score, 0)) as '物理',max(if(a.course = '化学', a.score, 0)) as '化学',max(if(a.course = '政治', a.score, 0)) as '政治'from test1 a group by a.name

-- 使用distinct
select distinct  a.name,  
(select score from test1 b where a.name=b.name and b.course='语文' ) as 'chinese',  
(select score from test1 b where a.name=b.name and b.course='数学' ) as 'math',
(select score from test1 b where a.name=b.name and b.course='英语' ) as 'english',  
(select score from test1 b where a.name=b.name and b.course='物理' ) as 'wuli',  
(select score from test1 b where a.name=b.name and b.course='化学' ) as 'huaxue'  
from test1 a

-- 使用with rollup统计第一种
select ifnull(name,'总计') as name,
max(if(course = '语文',score,0))as 'chinese',
max(if(course = '数学',score,0))as 'math',
max(if(course = '英语',score,0))as 'english',
max(if(course = '物理',score,0))as 'wuli',
max(if(course = '化学',score,0))as 'huaxue',
sum(if(course='total',score,0)) as 'total'
from (select name,ifnull(course,'total') as course,sum(score) as score
from test1 group by name, course with rollup having name is not null )as a group by name with rollup;


-- 使用with rollup与union all统计第二种
select name,
max(if(course = '语文',score,0))as 'chinese',
max(if(course = '数学',score,0))as 'math',
max(if(course = '英语',score,0))as 'english',
max(if(course = '物理',score,0))as 'wuli',
max(if(course = '化学',score,0))as 'huaxue',
sum(score) as total from test1 group by name
union all
select 'total',
max(if(course = '语文',score,0))as 'chinese',
max(if(course = '数学',score,0))as 'math',
max(if(course = '英语',score,0))as 'english',
max(if(course = '物理',score,0))as 'wuli',
max(if(course = '化学',score,0))as 'huaxue',
sum(score) from test1


-- 使用if与with rollup统计
select ifnull(name,'total') as name,
max(if(course = '语文',score,0))as 'chinese',
max(if(course = '数学',score,0))as 'math',
max(if(course = '英语',score,0))as 'english',
max(if(course = '物理',score,0))as 'wuli',
max(if(course = '化学',score,0))as 'huaxue',
sum(score) as total 
from test1
group by name with rollup

-- 使用group_concat，这个一般不推荐
select name,
group_concat(course,':',score separator '@')as course
from test1 group by name

-- 这个也不推荐使用
set @ee='';
select @ee :=concat(@ee,'sum(if(course= \'',course,'\',score,0)) as ',course, ',') as aa from (select distinct course from test1) a ;
set @qq = concat('select ifnull(name,\'total\')as name,',@ee,' sum(score) as total from test1 group by name with rollup');
-- select @qq;
prepare stmt from @qq;
execute stmt;
deallocate prepare stmt;

create table test2(
id int  auto_increment primary key ,
name varchar(255) ,
chinese int,
math int,
english int,
wuli int,
huaxue int
)

insert into test2(name,chinese,math,english,wuli,huaxue) values ('张三',110,120,85,null,null);
insert into test2(name,chinese,math,english,wuli,huaxue) values ('李四',130,88,89,null,null);
insert into test2(name,chinese,math,english,wuli,huaxue) values ('王五',93,124,87,98,67);

union与union all的区别就是：
union可以去除重复结果集，union all不会去除重复的结果集  

-- 第一种使用union实现列传行
select name,'语文' as course, chinese as 'score' from test2 union 
select name,'数学' as course, math as 'score' from test2 union 
select name,'英语' as course, english as 'score' from test2 union 
select name,'物理' as course, wuli as 'score' from test2 union 
select name,'化学' as course, huaxue as 'score' from test2 order by name asc

-- 第二种写法可以对null值结果集处理
select * from
(select name,'语文' as course, chinese as 'score' from test2 union 
select name,'数学' as course, math as 'score' from test2 union 
select name,'英语' as course, english as 'score' from test2 union 
select name,'物理' as course, wuli as 'score' from test2 union 
select name,'化学' as course, huaxue as 'score' from test2)a
where a.score is not null order by name asc