MySQL中的列转行方法

数据如下：

​

create table test (
    id int (10) not null auto_increment primary key,
    name varchar(20) default null,
    course varchar(20) default null,
    score float default 0
);

insert into test(name,course,score) values
("张三", "数学", 34),
("张三", "语文", 58),
("张三", "英语", 58),
("李四", "数学", 45),
("李四", "语文", 87),
("李四", "英语", 45),
("王五", "数学", 76),
("王五", "语文", 34),
("王五", "英语", 89);


​select * from test;

 

转行之前

         

方法一

select name,
    max(case course when '数学' then score else 0 end) as 数学,
    max(case course when '语文' then score else 0 end) as 语文,
    max(case course when '英语' then score else 0 end) as 英语
from test 
group by name;

转行之后 

如果此处不加max 出来的结果如下：

 

方法二

select name,
    max(if(course = '数学',score,0)) as 数学,
    max(if(course = '语文',score,0)) as 语文,
    max(if(course = '英语',score,0)) as 英语
from test
group by name;

这两种方法适用于MySQL，

另外pivot 是SQL Server中的语句 MySQL不适用。