还是SQL语句！

[zxlkxy]

表A
AID,AA
A1, 30
A2, 40
A3, 50
表B
BID,BA, B_AID ，B_CID
1 , 20 ,A1 , C1
2, 3 ,A1 , C2
3, 4, A2 , C2
4, 5, NULL , C3
5, 6, A2 , C3
6, 10 ,NUll , C3
表C
CID, CA, CB
C1 , CA1 ,CB1
C2, CA3 ,CB1
C3, CA4 ,CB2

AID与B_AID 一对多的关系，B_AID 可以为空，当B_AID 为空时不处理，当B_AID ＝AID时，BA求和，当AID在表B中没有时，为0

CID 与B_CID一对多的关系,B_CID不能为空，当B_AID ＝AID时，BA求和时，取CA的最大值，当AID在表B中没有时，取NULL值。
要的结果：
A1,30,23,CA3
A2,40,10,CA4
A3,50,0, NULL

还是要求不能用UNION该如何写？

[愉快的登山者]

select A.AID, A.AA, sum(isnull(B.BA,0)), (select CA from C where CID = max(B.B_CID))
from A left join B on A.AID = B.B_AID

[txlicenhe]

Select AID,AA,IsNull(sum(IsNull(BA,0)),0),max(CA)
From A
Left join B on AID = B_AID
Left join C on CID = B_CID
group by AID,AA

[zxlkxy]

呵呵，我试试！

[zxlkxy]

用 txlicenhe(不做技术高手) 的方法搞定，谢谢！

[pengdali]

select *,(select isnull(sum(BA),0) from 表B where B_AID=表A.B_AID),(select max(CA) from 表C where CID in (select B_CID from 表B where B_AID=表A.B_AID)) from 表A

[zxlkxy]

用 tj_dns(愉快的登山者) 的方法也行！谢谢，给分啦！

[pengdali]

select *,(select isnull(sum(BA),0) from 表B where B_AID=表A.B_AID),(select max(CA) from 表C where CID in (select B_CID from 表B where B_AID=表A.B_AID)) from 表A

[CrazyFor]

select a.*,b.ba,c.ca
from a left join
(select b_aid,sum(isnull(ba,0))ba ,max(b_cid)b_cid from b group by b_aid) bb
on a.aid=b.b_aid
left join c on b.b_cid=c.cid