日期:2014-05-17 浏览次数:20547 次
--> 测试数据:[A1] go if object_id('[A1]') is not null drop table [A1] go create table [A1]( [id] int, [num] int, [class] varchar(5) ) go insert [A1] select 1,10,'类别1' union all select 1,101,'类别2' union all select 2,20,'类别1' union all select 2,50,'类别1' --> 测试数据:[B1] go if object_id('[B1]') is not null drop table [B1] create table [B1]( [id] int, [num] int, [class] varchar(5) ) go insert [B1] select 1,100,'类别1' union all select 1,1001,'类别2' union all select 1,1000,'类别3' union all select 2,20,'类别1' union all select 2,20,'类别2' go create table #t( id int, numa int, numb int, class varchar(5) ) insert #t select b.id,sum(isnull(a.num,0)) as numa,sum(isnull(b.num,0)) as numb,b.class from [B1] b left join [A1] a on b.id=a.id group by b.id,b.class select * from #t declare @str varchar(1000) set @str='' select @str=@str+','+class+'=sum(case when class=' +QUOTENAME(class,'''')+'then numa+numb else 0 end)' from #t group by class set @str='select id'+@str+' from #t group by id' exec(@str) ---------------------------------- id 类别1 类别2 类别3 1 311 2113 2111 2 110 110 0