复盘一下前两天MySQL的一道笔试题。
建表语句为：
create table score_1(
id VARCHAR(10),
course VARCHAR(10),
score float
);
insert INTO score_1 VALUES('张三','语文',80);
insert INTO score_1 VALUES('李四','数学',67);
insert INTO score_1 VALUES('王五','英语',60);
insert INTO score_1 VALUES('张三','数学',45);
insert INTO score_1 VALUES('李四','语文',83);
insert INTO score_1 VALUES('王五','语文',60);
insert INTO score_1 VALUES('张三','英语',98);
insert INTO score_1 VALUES('李四','英语',77);
insert INTO score_1 VALUES('王五','数学',89);
这是典型的行转列操作。方法有以下两种：
##方法1
select id,
max(case when course = '语文' then score else 0 end) as '语文',
max(case when course = '数学' then score else 0 end) as '数学',
max(case when course = '英语' then score else 0 end) as '英语',
sum(score) as total
from score_1 group by id;
##方法2
select id,
max(if(course = '语文',score,0)) as '语文',
max(if(course = '数学',score,0)) as '数学',
max(if(course = '英语',score,0)) as '英语',
sum(score) as total
from score_1 group by id;
注意：max() 是为了能够使用 group by 根据 id 进行分组，因为每一个 id 对应的course = ‘语文’ 的记录只有一条，所以 max() 的值就等于对应那一条记录的 score 的值。 因此sum()、min()、avg()等聚合函数都可以达到行转列的效果。
若要实现对每一列也求和，可以使用with rollup直接生成结果。
select ifnull(id,'total') as id,
max(case when course = '语文' then score else 0 end) as '语文',
max(case when course = '数学' then score else 0 end) as '数学',
max(case when course = '英语' then score else 0 end) as '英语',
sum(score) as total
from score_1 group by id with rollup;
转自：：https://www.jianshu.com/p/e09c4cb1da78