求一条好的统计SQL语句
表A 字段 A_UID A_Name
子表B 字段 B_UID A_UID B_NUMBER
字表C 字段 C_UID A_UID C_NUMBER
希望统计结果
A_UID
相关B_NUMBER的和
相关C_NUMBER的和
以下SQL语句效率太低
SELECT
A_UID,
(SELECT SUM(B_NUMBER) FROM B WHERE B.A_UID=A.A_UID) AS B_NUMBER_TOTAL,
(SELECT SUM(C_NUMBER) FROM C WHERE C.A_UID=A.A_UID) AS C_NUMBER_TOTAL
FROM A
不能使用视图，因为可能要加动态的查询条件和A B C表都相关
求一条SQL语句，能够获得理想的性能和结果
[解决办法]
select a.a_uid,sum(b.b_number) b_number , sum(c.c_number) c_number
from a
left b on a.a_uid = b.a_uid
left c on a.a_uid = c.a_uid
[解决办法]
--try
select * from A
left join(
select A_UID,sum(B_NUMBER) as B_NUMBER from B group by A_UID
)B on A.A_UID=B.A_UID
left join (
select A_UID,sum(C_NUMBER) as C_NUMBER from C group by A_UID
)C on A.A_UID=C.A_UID
[解决办法]
可以利用临时表啊
SELECT A_UID,SUM(B_NUMBER) B_NUMBER INTO #B FROM B WHERE B.A_UID IN (SELECT A.A_UID FROM A) GROUP BY A_UID
SELECT A_UID,SUM(C_NUMBER) C_NUMBER INTO #C FROM C WHERE C.A_UID IN (SELECT A.A_UID FROM A)
GROUP BY A_UID
SELECT A.A_UID,isnull(B_NUMBER,0),isnull(C_NUMBER,0) FROM A LEFT LEFT JOIN #B ON #B.A_UID=A.A_UID
LEFT JOIN #C ON #C.A_UID=A.A_UID
DROP TABLE #B
DROP TABLE #C
