下面二个表要合成一个表的数据
A表
FamilyCount FollowPersonID
1 42
126 44
33 50
B表
CompCount FollowPersonID
1 0
30 44
3 50
要合成下面的C表
FamilyCount CompCount FollowPersonID
1 0 42
0 1 0
126 30 44
33 3 50
根据FollowPersonID对A表和B表进行合并，如FollowPersonID 44，FamilyCount 为126，CompCount为30，要合成C表的数据，该如何写SQL
------解决思路----------------------
select isnull(FamilyCount,0),
case when a.FollowPersonID IS not null then a.FollowPersonID else b.FollowPersonID end,
isnull(CompCount,0) from
(select '1','42' union all select '126','44' union all select '33','50' )a(FamilyCount,FollowPersonID) full join
(select '1','0' union all select '30','44' union all select '3','50' )b(CompCount,FollowPersonID)
on a.FollowPersonID=b.FollowPersonID
------解决思路----------------------
select
isnull(t2.FamilyCount, 0),
isnull(t1.CompCount, 0),
isnull(t2.FollowPersonID, 0)
from B表 as t1
full join A表 as t2
on t1.FollowPersonID = t2.FollowPersonID