求一left /right join 语句，请大哥们帮忙哦！在线等待，解决立即结帖！！

[rr]

语句如下。。
Select (case when A.total is null then 0 else A.total end) - (case when B.total is null then 0 else B.total end)
from (select sum(LeftAmtOrig) as total,tAccountID
from t_ArBill where Status <> '3' and tAccountID = '22' GROUP BY tAccountID ) as A
--------------------------------下面是关键，请问如何实现？？谢谢哦
(case when A.total is null then right join
when B.total is null then left join
else join end)
------------------------------------
(select sum(LeftAmtOrig) as total,tAccountID from tReceiveBill
where Status <> '3' and checkFlag = 'A' and tAccountID = '22' GROUP BY tAccountID) AS B
on A.tAccountID = B.tAccountID

[lsxaa]

用动态SQL 语句吧
exec('...................')

[rr]

如何实现？？请大哥们帮写写呀。。谢谢哦！

[pbsql]

a,b都有记录则inner join,否则full join：
if exists(select * from t_ArBill where Status <> '3' and tAccountID = '22')
and exists(select * from tReceiveBill where Status <> '3' and checkFlag = 'A' and tAccountID = '22')
Select A.total - B.total
from (select isnull(sum(LeftAmtOrig),0) as total,tAccountID from t_ArBill
where Status <> '3' and tAccountID = '22' GROUP BY tAccountID ) as A
join
(select isnull(sum(LeftAmtOrig),0) as total,tAccountID from tReceiveBill
where Status <> '3' and checkFlag = 'A' and tAccountID = '22' GROUP BY tAccountID) AS B
on A.tAccountID = B.tAccountID
else
Select isnull(A.total,0) - isnull(B.total,0)
from (select isnull(sum(LeftAmtOrig),0) as total,tAccountID from t_ArBill
where Status <> '3' and tAccountID = '22' GROUP BY tAccountID ) as A
full join
(select isnull(sum(LeftAmtOrig),0) as total,tAccountID from tReceiveBill
where Status <> '3' and checkFlag = 'A' and tAccountID = '22' GROUP BY tAccountID) AS B
on A.tAccountID = B.tAccountID

[rr]

风云大哥，
谢谢你的指点。
其实只要这么写就可以了。。

Select (case when A.total is null then 0 else A.total end) - (case when B.total is null then 0 else B.total end)
from (select sum(LeftAmtOrig) as total,tAccountID
from t_ArBill where Status <> '3' and tAccountID = '22' GROUP BY tAccountID ) as A
--------------------------------下面是关键，请问如何实现？？谢谢哦
full join --这么改就可以解决我的问题了。看来我sql还没学好。
------------------------------------
(select sum(LeftAmtOrig) as total,tAccountID from tReceiveBill
where Status <> '3' and checkFlag = 'A' and tAccountID = '22' GROUP BY tAccountID) AS B
on A.tAccountID = B.tAccountID