– 数据
stu_score
id userid subject score
1,001,语文,90
2,001,数学,92
3,001,英语,80
4,002,语文,88
5,002,数学,90
6,002,英语,75.5
7,003,语文,70
8,003,数学,85
9,003,英语,90
10,003,政治,82
预期结果:
userid 语文 数学 英语 政治 总成绩
汇总 248.0 267.0 245.5 82.0 842.5
001 90.0 92.0 80.0 0.0 262.0
002 88.0 90.0 75.5 0.0 253.5
003 70.0 85.0 90.0 82.0 327.0
最初实现
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