样本数据
id | name | score |
---|---|---|
1 | aa | 30 |
2 | aa | 40 |
3 | aa | 50 |
1 | bb | 50 |
2 | bb | 40 |
3 | bb | 30 |
想转化成
name | score1 | score2 | scoer3 |
---|---|---|---|
aa | 30 | 40 | 50 |
bb | 50 | 40 | 30 |
方法一:
select b.name,sum(b.score1) score1,sum(b.score2) score2,sum(b.score3) score3
from (
select a.name,
case when id=1 then score else 0 end score1,
case when id=2 then score else 0 end score2,
case when id=3 then score else 0 end score3
from abc a
) b
group by b.name
方法二:
select name,
sum(case when id=1 then score else 0 end) score1,
sum(case when id=2 then score else 0 end) score2,
sum(case when id=3 then score else 0 end) score3
from abc
group by name