2019-12-01_行转列

有以下表

DROP TABLE IF EXISTS tb_score;

CREATE TABLE tb_score(

    id INT(11) NOT NULL auto_increment,

    userid VARCHAR(20) NOT NULL COMMENT '用户id',

    subject VARCHAR(20) COMMENT '科目',

    score DOUBLE COMMENT '成绩',

    PRIMARY KEY(id)

)ENGINE = INNODB DEFAULT CHARSET = utf8;

插入数据

INSERT INTO tb_score(userid,subject,score) VALUES ('001','语文',90);

INSERT INTO tb_score(userid,subject,score) VALUES ('001','数学',92);

INSERT INTO tb_score(userid,subject,score) VALUES ('001','英语',80);

INSERT INTO tb_score(userid,subject,score) VALUES ('002','语文',88);

INSERT INTO tb_score(userid,subject,score) VALUES ('002','数学',90);

INSERT INTO tb_score(userid,subject,score) VALUES ('002','英语',75.5);

INSERT INTO tb_score(userid,subject,score) VALUES ('003','语文',70);

INSERT INTO tb_score(userid,subject,score) VALUES ('003','数学',85);

INSERT INTO tb_score(userid,subject,score) VALUES ('003','英语',90);

INSERT INTO tb_score(userid,subject,score) VALUES ('003','政治',82);

要求：将列表转换为下图方式

语句：

解决方法1:

select userid,

SUM(case subject when '语文' then score else 0 end) as '语文',

SUM(case subject when '数学' then score else 0 end) as '数学',

SUM(case subject when '英语' then score else 0 end) as '英语',

SUM(case subject when '政治' then score else 0 end) as '政治'

from tb_score

group by userid

解决方法2：

SELECT userid,

SUM(IF(`subject`='语文',score,0)) as '语文',

SUM(IF(`subject`='数学',score,0)) as '数学',

SUM(IF(`subject`='英语',score,0)) as '英语',

SUM(IF(`subject`='政治',score,0)) as '政治'

FROM tb_score

GROUP BY userid