如果有表t1 ,t2t1包含了f1,f2,f3...int型字段
t2包含了f1,f2,f3...int型字段
如:
t1 t2
======================= =======================
f1 f2 f3 f1 f2 f3
======================= =======================
10 20 30 10 -20 30
20 30 40 20 30 -40
.... .... 现在希望生成一个查询
==========================================================
t1.f1 t1.f2 t1.f3 t2.f1 t2.f2 t2.f3 sum
==========================================================
10 20 30 10 -20 30 80
20 30 40 20 30 -40 120
.... .... ...
t2包含了f1,f2,f3...int型字段
如:
t1 t2
======================= =======================
f1 f2 f3 f1 f2 f3
======================= =======================
10 20 30 10 -20 30
20 30 40 20 30 -40
.... .... 现在希望生成一个查询
==========================================================
t1.f1 t1.f2 t1.f3 t2.f1 t2.f2 t2.f3 sum
==========================================================
10 20 30 10 -20 30 80
20 30 40 20 30 -40 120
.... .... ...
where t1.f1=t2.f1
select id = identity(int,1,1), * into #tmp2 from t2select a.f1,a.f2,a.f3,b.f1,b.f2,b.f3,
sum = (a.f1 + a.f2 + a.f3 + b.f1 + b.f2 + b.f3 )
from #tmp1 a
inner join @tmp2 b on a.id = b.iddrop table #tmp1,#tmp2
sum(t1.f1) as [t1.f1],
sum(t1.f2) as [t1.f2],
sum(t1.f3) as [t1.f3],
sum(t2.f1) as [t1.f1],
sum(t2.f2) as [t1.f2],
sum(t2.f3) as [t1.f3],
sum(
isnull(t1.f1,0)+
isnull(t1.f2,0)+
isnull(t1.f3,0)+
isnull(t2.f1,0)+
isnull(t2.f2,0)+
isnull(t2.f3,0)
) as [Sum]
from t1 a inner join t2 b on a.id=b.id
inner join @tmp2 b on a.id = b.id
应为
inner join #tmp2 b on a.id = b.id即:
select id = identity(int,1,1), * into #tmp1 from t1
select id = identity(int,1,1), * into #tmp2 from t2select a.f1,a.f2,a.f3,b.f1,b.f2,b.f3,
sum = (a.f1 + a.f2 + a.f3 + b.f1 + b.f2 + b.f3 )
from #tmp1 a
inner join #tmp2 b on a.id = b.iddrop table #tmp1,#tmp2
select * from t1,t2
where t1.关联字段=t2.关联字段
t1.f1 as [t1.f1],
t1.f2 as [t1.f2],
t1.f3 as [t1.f3],
t2.f1 as [t1.f1],
t2.f2 as [t1.f2],
t2.f3 as [t1.f3],
isnull(t1.f1,0)+
isnull(t1.f2,0)+
isnull(t1.f3,0)+
isnull(t2.f1,0)+
isnull(t2.f2,0)+
isnull(t2.f3,0) as [Sum]
from t1 a inner join t2 b on a.id=b.id