这样的分类汇总如何写SQL语句？

[china2007]

表1 表2 表3
id name id na 重量 na 类型
01 a 01 1 10 1 s
02 b 01 2 20 2 s
03 c 01 3 50 3 w
04 d 03 4 40 4 w

要求得到
name 类型(s)重量 类型(w)重量
a 30 50
b 0 0
c 0 40
d 0 0

[playyuer]

select max(a.name)
,sum(case when c.类型 = 's' then b.重量 else 0 end) as 类型s重量
,sum(case when c.类型 = 'w' then b.重量 else 0 end) as 类型s重量
from 表1 a
left join 表2 b
on a.id = b.id
left join 表3 c
on b.na = c.na
group by a.id

[didoleo]

create table ta
(id varchar(2),name nvarchar(1))


create table tb
(id varchar(2),na int, 重量 int)


create table tc
(na int, 类型 nvarchar(1))



id name id na 重量 na 类型
01 a 01 1 10 1 s
02 b 01 2 20 2 s
03 c 01 3 50 3 w
04 d 03 4 40 4 w

insert into ta
select '01' , 'a' union all
select '02' , 'b' union all
select '03' , 'c' union all
select '04' , 'd'


insert into tb
select '01' , 1 , 10 union all
select '01' , 2 , 20 union all
select '01' , 3 , 50 union all
select '03' , 4 , 40


insert into tc
select 1 , 's' union all
select 2 , 's' union all
select 3 , 'w' union all
select 4 , 'w'


select * from ta
select * from tb
select * from tc

declare @s nvarchar(4000)
set @s=''
select @s=@s+',[类型('+类型+')重量]=sum(isnull(case c.类型 when '''+类型+''' then 重量 else NULL end,0))'
from tc group by 类型
select @s='select a.name'+@s+' from ta a left join tb b on a.id=b.id left join tc c on b.na=c.na group by a.name'
exec(@s)

----------------------------

a 30 50
b 0 0
c 0 40
d 0 0

[playyuer]

select max(a.name)
,sum(case when c.类型 = 's' then b.重量 else 0 end) as [类型(s)重量]
,sum(case when c.类型 = 'w' then b.重量 else 0 end) as [类型(w)重量]
from 表1 a
left join 表2 b
on a.id = b.id
left join 表3 c
on b.na = c.na
group by a.id

[didoleo]

--测试数据
create table ta
(id varchar(2),name nvarchar(1))
create table tb
(id varchar(2),na int, 重量 int)
create table tc
(na int, 类型 nvarchar(1))

insert into ta
select '01' , 'a' union all
select '02' , 'b' union all
select '03' , 'c' union all
select '04' , 'd'

insert into tb
select '01' , 1 , 10 union all
select '01' , 2 , 20 union all
select '01' , 3 , 50 union all
select '03' , 4 , 40


insert into tc
select 1 , 's' union all
select 2 , 's' union all
select 3 , 'w' union all
select 4 , 'w'



declare @s nvarchar(4000)
set @s=''
select @s=@s+',[类型('+类型+')重量]=sum(isnull(case c.类型 when '''+类型+''' then 重量 else NULL end,0))'
from tc group by 类型
select @s='select a.name'+@s+' from ta a left join tb b on a.id=b.id left join tc c on b.na=c.na group by a.name'
exec(@s)
------------------------------------------
name 类型(s)重量 类型(w)重量
a 30 50
b 0 0
c 0 40
d 0 0