Mysql数据库行转列

--学生表

create table students(
sname varchar(20),
course varchar(20),
scores varchar(20)

)

insert into students(sname,course,scores)
VALUES('张三','语文','70');
insert into students(sname,course,scores)
VALUES('张三','数学','80');
insert into students(sname,course,scores)
VALUES('张三','英语','90');
insert into students(sname,course,scores)
VALUES('李四','语文','75');
insert into students(sname,course,scores)
VALUES('李四','数学','85');
insert into students(sname,course,scores)

VALUES('李四','英语','95');

1.利用max(CASE ... WHEN ... THEN .. ELSE END) AS "语文"的方式来实现

select sname 姓名,
    Max(case course when '语文' then scores else 0 end ) 语文,
    Max(case course when '数学' then scores else 0 end ) 数学,
    Max(case course when '英语' then scores else 0 end ) 英语
from students

GROUP BY sname;

--运行结果为：

2、利用SUM(IF()) 生成列

select sname 姓名,
SUM(IF(course='语文',scores,0)) 语文,
SUM(IF(course='数学',scores,0)) 数学,
SUM(IF(course='英语',scores,0)) 英语
from students

GROUP BY sname;

--运行结果为：