SQL 行转列 写法

 准备如下表:

create table if not exists score(
    id int,
    userid int,
    subject varchar(32),
    score double
);

insert into score(id,userid,subject,score) values(1,1001,'语文',81);
insert into score(id,userid,subject,score) values(2,1001,'数学',82);
insert into score(id,userid,subject,score) values(3,1001,'英语',83);
insert into score(id,userid,subject,score) values(4,1002,'语文',91);
insert into score(id,userid,subject,score) values(5,1002,'数学',92);
insert into score(id,userid,subject,score) values(6,1002,'英语',93);
insert into score(id,userid,subject,score) values(7,1003,'语文',71);
insert into score(id,userid,subject,score) values(8,1003,'数学',72);
insert into score(id,userid,subject,score) values(9,1003,'英语',73);

select * from score;

一:case-when-end

select  
userid,
sum(case when `subject` = '语文' then score else 0 end) as '语文',
sum(case when `subject` = '数学' then score else 0 end) as '数学',
sum(case when `subject` = '英语' then score else 0 end) as '英语'
from score group by userid

二:if

select 
userid,
sum(if(`subject` = '语文',score,0)) as '语文',
sum(if(`subject` = '数学',score,0)) as '数学',
sum(if(`subject` = '英语',score,0)) as '英语'
from score
group by userid