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 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_usergroup by name