求一条SQL语句 - MS-SQL Server / 基础类

有表A：A_ID,A_Value
表B：B_ID,B_A
表C：C_ID,C_A
其中B_A作为外键关联到A_ID，C_A作为外键关联到A_ID。B_A和C_A均可能为NULL。

现在想用一条SQL语句查询出A_ID,A_Value,以及B表中B_A等于A_ID的记录总数，C表中C_A等于A_ID的记录总数。
伪代码：
SELECT A_ID,A_Value,COUNT(B.*) WHERE B_A=A_ID,COUNT(C.*) WHERE C_A=A_ID from A

这个应该怎么写呢？

---

SQL code:
SELECT A_ID,COUNT(B.B_A)AS NUM1,COUNT(C_A)AS NUM2
from A ,B,C WHERE A.A_ID=B.B_a
AND A.A_ID=C.C_A
GROUP BY A_ID

---

SQL code:
select a.*,(select count(1) from b where b_a=aa.a_id) b数,(select count(1) from c where c_a=aa.a_id) c数
from a aa

---

引用

SQL code
SELECT A_ID,COUNT(B.B_A)AS NUM1,COUNT(C_A)AS NUM2
from A ,B,C WHERE A.A_ID=B.B_a
AND A.A_ID=C.C_A
GROUP BY A_ID

up

---

SQL code:
select A.A_ID,A.A_Value,COUNT(b.B_A) as B_A_Count,COUNT(c.C_A) as C_A_Count
from tbA as a
left join tbB as b
on a.A_ID=b.B_A
LEFT JOIN tbC as c
on A.A_ID=c.C_A
group by A.A_ID,A.A_Value

---

SQL code

---

Code highlighting produced by Actipro Co