Mysql行转列

准备表数据

CREATE TABLE test_user (
  name varchar(50) DEFAULT NULL,
  subject varchar(50) DEFAULT NULL,
  score int(11) DEFAULT NULL
);
insert into test_user values
('zhangsan' , 'chinese' , 10),
('zhangsan' , 'math' , 20),
('zhangsan' , 'english' , 30),
('lily' , 'chinese' , 40),
('lily' , 'math' , 50),
('lily' , 'english' , 60),
('mini' , 'chinese' , 70),
('mini' , 'math' , 80),
('mini' , 'english' , 90);

select * from test_user

行转列

select name,
 max(IF(subject = 'chinese',score,0)) as 'chinese',
 max(IF(subject = 'math',score,0)) as 'math',
 max(IF(subject = 'english',score,0)) as 'english',
 sum(score) as'total'
from test_user
group by name