试试这个: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
select t1.name,
t1.a * t2.a a,
t1.b * t2.b b,
t1.c * t2.c c
from #temp1 t1
inner join #temp2 t2
on t1.name = t2.name
/*
name a b c
a 4 4 4
b 6 6 6
c 6 6 6
d 4 4 4
*/
drop table #temp1
drop table #temp2
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
select t1.name,
t1.a * t2.a a,
t1.b * t2.b b,
t1.c * t2.c c
from #temp1 t1
inner join #temp2 t2
on t1.name = t2.name
/*
name a b c
a 4 4 4
b 6 6 6
c 6 6 6
d 4 4 4
*/
drop table #temp1
drop table #temp2
不知如何定位除NAME外的其它字段.以便来计算.
试试这个:--生产动态语句,适应动态列
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.colidwhere 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
*/
select t1.name,
t1.a * t2.a a,
t1.b * t2.b b,
t1.c * t2.c c
from #temp1 t1
inner join #temp2 t2
on t1.name = t2.name
再次谢过,佩服佩服.....