select a.f1,a.f2,isnull(a.f3,0)-isnull(b.f3,0) as f3 from (select f1,f2,sum(f3) as f3 from t1 group by f1,f2 ) a left join (select f1,f2,sum(f3) as f3 from t2 group by f1,f2 ) b on a.f1=b.f1 and a.f2=b.f2 union all select b.f1,b.f2,isnull(a.f3,0)-isnull(b.f3,0) as f3 from (select f1,f2,sum(f3) as f3 from t1 group by f1,f2 ) a right join (select f1,f2,sum(f3) as f3 from t2 group by f1,f2 ) b on a.f1=b.f1 and a.f2=b.f2 ------------------------------- 给你一个笨的方法
SELECT f1 = ISNULL(A.f1,B.f1), f2 = ISNULL(A.f2,B.f2), f3 = ISNULL(A.f3,0) - ISNULL(B.f3,0) FROM T1 A FULL OUTER JOIN T2 B ON A.f1=B.f1 AND A.f2 = B.f2
a.f1,a.f2,isnull(a.f3,0)-isnull(b.f3,0) as f3
from (select
f1,f2,sum(f3) as f3
from t1
group by f1,f2
) a
left join
(select f1,f2,sum(f3) as f3
from t2
group by f1,f2
) b
on a.f1=b.f1 and a.f2=b.f2
union all
select
b.f1,b.f2,isnull(a.f3,0)-isnull(b.f3,0) as f3
from (select
f1,f2,sum(f3) as f3
from t1
group by f1,f2
) a
right join
(select f1,f2,sum(f3) as f3
from t2
group by f1,f2
) b
on a.f1=b.f1 and a.f2=b.f2
-------------------------------
给你一个笨的方法
1、若t1有,t2没有怎么处理?
2、若t2有,t1没有怎么处理?
3、空值怎么处理?
f1 = ISNULL(A.f1,B.f1),
f2 = ISNULL(A.f2,B.f2),
f3 = ISNULL(A.f3,0) - ISNULL(B.f3,0)
FROM
T1 A
FULL OUTER JOIN
T2 B
ON
A.f1=B.f1 AND A.f2 = B.f2