有一个表#t1,数据如下:f1 f2 f3 f4 f5 f6
A001 A -1 1 0 0
A002 B 1 0 -1
表#t2的数据如下:f1 f2 f3 f4 f5 f6
A001 A 0 0 12 0
A002 B 0 0 14 0
A003 C 0 0 -8 0
A003 C 0 0 10 0
我想把#t2的数据合并到#t1里去,只要是#t2.f1=#t1.f1 and #t2.f2=#t1.f2,只是把#t2.f5的数据合并到#t1的f5里去,结果是:f1 f2 f3 f4 f5 f6
A001 A -1 1 12 0
A002 B 1 14 -1
A003 C 0 0 2 0
这个sql语句如何来写?
A001 A -1 1 0 0
A002 B 1 0 -1
表#t2的数据如下:f1 f2 f3 f4 f5 f6
A001 A 0 0 12 0
A002 B 0 0 14 0
A003 C 0 0 -8 0
A003 C 0 0 10 0
我想把#t2的数据合并到#t1里去,只要是#t2.f1=#t1.f1 and #t2.f2=#t1.f2,只是把#t2.f5的数据合并到#t1的f5里去,结果是:f1 f2 f3 f4 f5 f6
A001 A -1 1 12 0
A002 B 1 14 -1
A003 C 0 0 2 0
这个sql语句如何来写?
select f1,f2,sum(f3) f3,sum(f4) f4,sum(f5) f5
from(
select * from #t1
union all
select * from #t2
)t
group by f1,f2
update t1 set f5 = f5 + isnull((select sum(f5) from t2 where t2.f1 = t1.f1 and t2.f2 = t1.f2),0)--不存在,怎么办?
use Tempdb
go
--> -->
if not object_id(N'Tempdb..#T') is null
drop table #T
Go
Create table #T([f1] nvarchar(4),[f2] nvarchar(1),[f3] int,[f4] int,[f5] int,[f6] int)
Insert #T
select N'A001',N'A',-1,1,0,0 union all
select N'A002',N'B',NULL,1,0,-1
Go--> -->
if not object_id(N'Tempdb..#T2') is null
drop table #T2
Go
Create table #T2([f1] nvarchar(4),[f2] nvarchar(1),[f3] int,[f4] int,[f5] int,[f6] int)
Insert #T2
select N'A001',N'A',0,0,12,0 union all
select N'A002',N'B',0,0,14,0 union all
select N'A003',N'C',0,0,-8,0 union all
select N'A003',N'C',0,0,10,0
GoSELECT f1 , f2,f3=MAX(f3),f4=MAX(f4),f5=SUM(f5),f6=min(f6)
FROM
(SELECT * FROM #T
UNION all
Select * from #T2
)t
GROUP BY
f1, f2/*
f1 f2 f3 f4 f5 f6
---- ---- ----------- ----------- ----------- -----------
A001 A 0 1 12 0
A002 B 0 1 14 -1
A003 C 0 0 2 0
*/
go
--> -->
if not object_id(N'Tempdb..#T') is null
drop table #T
Go
Create table #T([f1] nvarchar(4),[f2] nvarchar(1),[f3] int,[f4] int,[f5] int,[f6] int)
Insert #T
select N'A001',N'A',-1,1,0,0 union all
select N'A002',N'B',NULL,1,0,-1
Go--> -->
if not object_id(N'Tempdb..#T2') is null
drop table #T2
Go
Create table #T2([f1] nvarchar(4),[f2] nvarchar(1),[f3] int,[f4] int,[f5] int,[f6] int)
Insert #T2
select N'A001',N'A',0,0,12,0 union all
select N'A002',N'B',0,0,14,0 union all
select N'A003',N'C',0,0,-8,0 union all
select N'A003',N'C',0,0,10,0
GoUPDATE b
SET f5=a.[f5]+b.f5
FROM (Select f1,f2,SUM(f5) AS f5 from #T2 GROUP BY f1,f2) a INNER JOIN #T AS b ON a.[f1]=b.[f1] AND a.[f2]=b.[f2]INSERT INTO #T
( f1, f2, f3, f4, f5, f6 )
Select f1,f2,MAX([f3]),MAX([f4]),SUM(f5) AS f5 ,MIN([f6])
from #T2 AS a
WHERE NOT EXISTS(SELECT 1 FROM #T WHERE f1=a.f1 AND f2=a.f2)
GROUP BY f1,f2SELECT * FROM #T
/*
f1 f2 f3 f4 f5 f6
A001 A -1 1 12 0
A002 B NULL 1 14 -1
A003 C 0 0 2 0
*/
f1 f2 f3 f4 f5 f6
-------------------------------------------------- ---------- ----------- ----------- ----------- -----------
A001 A -1 1 12 0
A002 B NULL 1 14 -1
*/
update t1 set f5= (select SUM(f5) as f5 from t2 where t1.f1= f1 group by f1,f2) 这样不可以吗?同样的贴我把上次的COPY过来了。
from(
select * from #t1
union all
select * from #t2
)t
group by f1,f2