标量子查询优化--求解
各位大神,我有一个SQL想求优化方案,请大神们多多指导。
SQL :
SELECT (SELECT E.AMOUNT FROM D,E,F WHERE D.ID=E.DID AND E.ID=F.EID and E.EID IN (1,2,3) ) C1,
(SELECT E.AMOUNT FROM D,E,F WHERE D.ID=E.DID AND E.ID=F.EID and E.EID IN (4,5,6) ) C2,
(SELECT E.AMOUNT FROM D,E,F WHERE D.ID=E.DID AND E.ID=F.EID and E.EID IN (7,8,9) ) C3,
A.AMOUNT*C1,B.NUM*C2,C*C3
FROM A
JOIN B ON A.ID=B.AID
JOIN C ON C.BID=B.ID
WHERE B IN (1,2,3) AND C IN (4,5,7)
大概是以上的SQL,因为原始SQL保密请大家见谅,
我的主要目的想把标量子查询给做个优化,我目前只能想到通过with来修改思路如下,看看各位大神还有什么修改方法。
with t as (select e.amount from D,E,F WHERE D.ID=E.DID AND E.ID=F.EID and E.EID IN (1,2,3,4,5,6,7,8,9))