获取多表数据并集
前段时间有需求需要完成一个功能,如下例:
表A:【查询结果集】
HoID AccID AmountA
1 2 100
2 5 50
表B:【查询结果集】
HoID AccID AmountB
1 2 20
1 6 20
表C:【查询结果集】
HoID AccID AmountC
2 5 30
3 9 50
需要得到的结果是: ABC三表 HoID,AccID取并集,对应的AmountA,AmountB,AmountC有数据则显示,没有则0
HoID AccID AmountA AmountB AmountC
1 2 100 20 0
1 6 0 20 0
2 5 50 0 30
3 9 0 0 50
实现该功能时,考虑到三个表应该都属于主表,所以换了一种方式实现该功能:
With A as (), B as (), C as ()
select SUM.HoID,SUM.AccID, Isnull(A.AmountA,0) , Isnull(B.AmountB,0), Isnull(C.AmountC,0)
from (
select distinct HoID,AccID
from (
select HoID,AccID from A
union
select HoID,AccID from B
union
select HoID,AccID from C
)
) SUM
left join A on A.HoID = SUM.HoID and A.AccID= SUM.AccID
left join B on B.HoID = SUM.HoID and B.AccID= SUM.AccID
left join C on C.HoID = SUM.HoID and C.AccID= SUM.AccID
这样就可以解决之前纠结的问题的,哈哈~