日期:2014-05-16 浏览次数:20682 次
create table #temp1(
name char(2),
a int,
b int,
c int)
insert into #temp1 values('a',1,1,1)
insert into #temp1 values('b',2,2,2)
insert into #temp1 values('c',3,3,3)
insert into #temp1 values('d',4,4,4)
create table #temp2(
name char(2),
a int,
b int,
c int)
insert into #temp2 values('a',4,4,4)
insert into #temp2 values('b',3,3,3)
insert into #temp2 values('c',2,2,2)
insert into #temp2 values('d',1,1,1)
select * from #temp1
select * from #temp2
drop table #temp1
drop table #temp2
--生产动态语句,适应动态列
declare @sql varchar(8000)
set @sql =''
select @sql = @sql + ',t1.'+c.name+' * t2.'+cc.name+' as '+cc.name
from tempdb..syscolumns c
inner join tempdb..syscolumns cc
on c.colid = cc.colid
where c.id = OBJECT_ID('tempdb..#temp1')
and cc.id = OBJECT_ID('tempdb..#temp2')
and c.colid > 1
set @sql = 'select t1.name'+@sql +
' from #temp1 t1
inner join #temp2 t2
on t1.name = t2.name'
exec(@sql)
/*
name a b c
a 4 4 4
b 6 6 6
c 6 6 6
d 4 4 4
*/