SQL经典查询
网上转的,挺经典的
SQL查询 pid pnum sid
1 10 1
1 12 2
2 8 3
3 11 1
3 10 3
写一个查询语句使其表现为：
pid s1id s2id s3id
1 10 12 0
2 0 0 8
3 11 0 10
解决办法：
select pid,
sum(case when sid =1 then pnum else 0 end) as 's1id',
sum(case when sid =2 then pnum else 0 end) as 's2id',
sum(case when sid =3 then pnum else 0 end) as 's3id'
from xyz（表名）
group by pid
order by pid
1 10 1
1 12 2
2 8 3
3 11 1
3 10 3
写一个查询语句使其表现为：
pid s1id s2id s3id
1 10 12 0
2 0 0 8
3 11 0 10
解决办法：
select pid,
sum(case when sid =1 then pnum else 0 end) as 's1id',
sum(case when sid =2 then pnum else 0 end) as 's2id',
sum(case when sid =3 then pnum else 0 end) as 's3id'
from xyz（表名）
group by pid
order by pid
