Create Table A
(ID Int,
F1 Int,
F2 Int)
Insert A Select 1, 7, 2
Union All Select 2, 4, 5
Create Table B
(ID Int,
F3 Int,
F4 Int)
Insert B Select 1, 1, 3
Union All Select 4, 2, 1
GO
Select
IsNull(A.ID,B.ID) As ID,
IsNull(F1,0) As F1,
IsNull(F2,0) As F2,
IsNull(F3,0) As F3,
IsNull(F4,0) As F4
From A
Full Outer Join B
On A.ID=B.ID
GO
Drop Table A,B
--或:
SELECT ID, F1 = SUM(F1), F2= SUM(F2), F2= SUM(F3), F2= SUM(F4)
FROM(
SELECT ID, F1, F2, F3 = 0, F4 = 0 FROM A
UNION ALL
SELECT ID, F1 = 0, F2 = 0, F3, F4 FROM B
)A
GROUP BY ID
SELECT ID = ISNULL(A.ID, B.ID),
F1 = ISNULL(A.F1, 0),
F2 = ISNULL(A.F2, 0),
F3 = ISNULL(B.F3, 0),
F4 = ISNULL(B.F4, 0)
FROM A
FULL JOIN B
ON A.ID = B.ID