50个表,以三个表为例吧
select isnull(a.a,isnull(b.a,c.a)) as a,a.b as t1,b.b as t2,c.b as t3
bsum=isnull(a.b,0)+isnull(b.b,0)+isnull(c.b,0)
from t1 a full join t2 b on a.a=b.a
full join t3 c on a.a=c.a
select isnull(a.a,isnull(b.a,c.a)) as a,a.b as t1,b.b as t2,c.b as t3
bsum=isnull(a.b,0)+isnull(b.b,0)+isnull(c.b,0)
from t1 a full join t2 b on a.a=b.a
full join t3 c on a.a=c.a
create table t2(a char(10), b int)
create table t3(a char(10), b int)insert into t1 select
'1a', 2 union select
'1b', 6 union select
'1c', 1 insert into t2 select '1b', 3 union select
'1d ', 7 union select
'1e ', 2 union select
'1f ', 4
insert into t3 select
'1a ', 3 union select
'1c ', 5 union select
'1g ', 8 union select
'1h ', 9 union select
'1i ', 1 union select
'1j', 20drop table result
create table result(a char(10) )
declare @i int, @str varchar(2000), @column varchar(10),@tmpTbl varchar(10),@bSum varchar(8000)select @i=1
while @i<=3
begin
select @column='b'+convert(varchar(2),@i)
select @tmpTbl='t'+convert(varchar(2),@i)
select @str= ' alter table result add ' +@column +' int not null default 0 '
exec (@str)
select @str='insert into result(a,'+@column+') select a,b from '+ @tmpTbl
exec(@str)
select @i=@i+1, @bSum=isNull(@bSum,'') + @column + '+'
endalter table result add bSum int
select @str=
'update a set a.bSum=b.bSum
from result a JOin
(select a, bsum='+left(@bSum, len(@bSum)-1 )+' from result) b on a.a=b.a'
exec (@str)select * from resultdrop table result
drop table t1
drop table t2
drop table t3a b1 b2 b3 bSum
---------- ----------- ----------- ----------- -----------
1a 2 0 0 2
1b 6 0 0 6
1c 1 0 0 1
1b 0 3 0 3
1d 0 7 0 7
1e 0 2 0 2
1f 0 4 0 4
1a 0 0 3 2
1c 0 0 5 1
1g 0 0 8 8
1h 0 0 9 9
1i 0 0 1 1
1j 0 0 20 20
一下代码中的drop table result 漏注释掉了, 请楼主注释掉
drop table result
create table result(a char(10) )
declare @i int, @str varchar(2000), @column varchar(10),@tmpTbl varchar(10),@bSum varchar(8000)
where t1.id=t2.id and t1.id=t2.id ....
ordey by t1.id
create table t1(a char(10), b int)
create table t2(a char(10), b int)
create table t3(a char(10), b int)insert into t1 select
'1a', 2 union select
'1b', 6 union select
'1c', 1 insert into t2 select '1b', 3 union select
'1d ', 7 union select
'1e ', 2 union select
'1f ', 4
insert into t3 select
'1a ', 3 union select
'1c ', 5 union select
'1g ', 8 union select
'1h ', 9 union select
'1i ', 1 union select
'1j', 20--drop table result
create table result(a char(10) )
declare @i int, @str varchar(2000), @column varchar(10),@tmpTbl varchar(10),@bSum varchar(8000),@sum varCHar(8000)select @i=1
while @i<=3
begin
select @column='b'+convert(varchar(2),@i)
select @tmpTbl='t'+convert(varchar(2),@i)
select @str= ' alter table result add ' +@column +' int not null default 0 '
exec (@str)
select @str='insert into result(a,'+@column+') select a,b from '+ @tmpTbl
exec(@str)
select @i=@i+1, @bSum=isNull(@bSum,'') + @column + '+' ,@sum=isNull(@sum,'')+@column+'=sum('+ @column +')' + ','
end
alter table result add bSum int not null default 0
select @str=
'update result set bSum='+left(@bSum, len(@bSum)-1 )+' from result '
exec (@str)
select * from result
select @str=
'select a, '+left(@sum,len(@sum)-1) +',bSum=sum(bSum) from result group by a 'exec( @str)
drop table result
drop table t1
drop table t2
drop table t3
as
set nocount on
declare @s1 varchar(8000),@s2 varchar(8000),@s3 varchar(8000),@s4 varchar(8000),@i varchar(10)
select @s1='',@s2='',@s3='',@s4='',@i=0
select @i=@i+1
,@s1=case
when @i=1 then 'a1.a'
else 'isnull('+@s1+',a'+@i+'.a)' end
,@s2=@s2+',b'+@i+'=isnull(a'+@i+'.b,'''')'
,@s3=case
when @i=1 then 'isnull(a'+@i+'.b,'''')'
else @s3+'+isnull(a'+@i+'.b,'''')' end
,@s4=case
when @i=1 then '['+name+'] a1'
else @s4+' full join ['+name+'] a'+@i
+' on a1.a=a'+@i+'.a' end
from sysobjects
where objectproperty(id,N'isusertable')=1
and name like 't[0-9]%' --处理以t开头的表,这个查询条件你可以自己修改if @@rowcount=0 return --如果找不到表,直接返回exec('
select a='+@s1+'
'+@s2+'
,bSum=('+@s3+')
from '+@s4+'
')
go
create table t1(a varchar(10),b int)
insert t1 select '1a',2
union all select '1b',6
union all select '1c',1 create table t2(a varchar(10),b int)
insert t2 select '1b',3
union all select '1d',7
union all select '1e',2
union all select '1f',4create table t3(a varchar(10),b int)
insert t3 select '1a',3
union all select '1c',5
union all select '1g',8
union all select '1h',9
union all select '1i',1
union all select '1j',20
go--查询处理的通用存储过程
create proc p_qry
as
set nocount on
declare @s1 varchar(8000),@s2 varchar(8000),@s3 varchar(8000),@s4 varchar(8000),@i varchar(10)
select @s1='',@s2='',@s3='',@s4='',@i=0
select @i=@i+1
,@s1=case
when @i=1 then 'a1.a'
else 'isnull('+@s1+',a'+@i+'.a)' end
,@s2=@s2+',b'+@i+'=isnull(a'+@i+'.b,'''')'
,@s3=case
when @i=1 then 'isnull(a'+@i+'.b,'''')'
else @s3+'+isnull(a'+@i+'.b,'''')' end
,@s4=case
when @i=1 then '['+name+'] a1'
else @s4+' full join ['+name+'] a'+@i
+' on a1.a=a'+@i+'.a' end
from sysobjects
where objectproperty(id,N'isusertable')=1 and name like 't%'
if @@rowcount=0 return --如果找不到表,直接返回exec('
select a='+@s1+'
'+@s2+'
,bSum=('+@s3+')
from '+@s4+'
')
go--调用
exec p_qry
go--删除测试
drop table t1,t2,t3
drop proc p_qry
/*--测试结果a b1 b2 b3 bSum
---------- ----------- ----------- ----------- -----------
1b 6 3 0 9
1d 0 7 0 7
1e 0 2 0 2
1f 0 4 0 4
1c 1 0 5 6
1a 2 0 3 5
1j 0 0 20 20
1i 0 0 1 1
1h 0 0 9 9
1g 0 0 8 8
--*/