SQL 的多列统计

create table #type(id int , type varchar(30))
insert into #type
select  1  ,'中国'
union
select 2  ,'美国'
union
select 3 , '俄罗斯'

create table #timu(id int ,typeid int ,score int )
insert into #timu
select         1    ,    1    ,    3
union select 2    ,    1    ,    4
union select 3    ,    1    ,    5
union select 4    ,    2    ,    3
union select 5    ,    2    ,    4
union select 6    ,    3    ,    5

create table #paper(id int ,qid int   )
insert into #paper
select            1 , 1
union select    1 , 2
union select    1 , 3
union select    1 , 5
union select    2 ,1
union select    2 ,2
union select    2 ,6

select  p.id ,  sum(china.score) as 中国 , sum(am.score) as 美国 , sum(a.score) as  俄罗斯
from #paper as p
left join (select id ,score from #timu where typeid =1 ) as china on ( p.qid = china.id )
left join (select id ,score from #timu where typeid =2 ) as am on ( p.qid = am.id )
left join (select id ,score from #timu where typeid =3 ) as a on ( p.qid = a.id )
group by p.id

select  *
from #paper as p
--join (select id ,score from #timu where typeid =1 ) as china on ( p.qid = china.id )
join (select id ,score from #timu where typeid =2 ) as am on ( p.qid = china.id )
join (select id ,score from #timu where typeid =3 ) as a on ( p.qid = china.id )
group by p.id


结果:

id          中国          美国          俄罗斯
----------- ----------- ----------- -----------
1           12          4           NULL
2           7           NULL        5
警告: 聚合或其他 SET 操作消除了空值。

(2 行受影响)