SQL查询和优化（十二）

下等连接的标量子查询的改写
上一篇的例子中，主表中没有过滤条件。当主表中有过滤条件时，按上一篇的方法就会产生不必要的访问量

SELECT s.stkcode,        t.mktcode,        t.stype,        t.sname,        (SELECT SUM(c.hsl)        FROM c         WHERE c.stkcode = s.stkcode        AND c.mktcode = t.mktcode        AND c.calcdate BETWEEN to_char(to_date(s.tdate,'yyyymmdd')-365,'yyyymmdd')AND s.tdate)AS f1,        (SELECT decode(COUNT(C.CALCDATE),0,null,SUM(c.hsl)/COUNT(c.calcdate))        FROM c        WHERE c.stkcode = s.stkcode        AND c.mktcode = t.mktcode        AND c.calcdate BETWEEN to_char(to_date(s.tdate,'yyyymmdd')-365,'yyyymmdd')AND s.tdate)AS f2,        s.tdate    FROM s,t    WHERE s.stkcode = t.scode      AND t.status = 1      AND t.stype = 2      AND s.tdate >= to_number(to_char(SYSDATE -3,'YYYYMMDD'));

这各标量有以下两种必定方法：1、把标题数据单独提出来与主表进行JOIN操作，因有区间条件，这时，主表做再次JOIN2、主表直接与标量进行JOIN操作，然后汇总数据两种改写方法都需要把主查询放在WITH语句中WITH t0 AS (SELECT rownum AS sn,s.stkcode, t.mktcode,t.stype,t.sname,s.tdate FROM s,t WHERE s.stkcode = t.scode      AND t.status = 1      AND t.stype = 2      AND s.tdate >= to_number(to_char(SYSDATE -3,'YYYYMMDD')))这个语句中有两个表，不能返回rowid，所以使用rownum as sn 当作唯一标识改写一：先分组汇总，再关联WITH t0 AS (SELECT rownum AS sn,s.stkcode, t.mktcode,t.stype,t.sname,s.tdate FROM s,t WHERE s.stkcode = t.scode      AND t.status = 1      AND t.stype = 2      AND s.tdate >= to_number(to_char(SYSDATE -3,'YYYYMMDD')))SELECT t.stkcode, t.mktcode, t.stype,t.sname,t.tdate,c.f2,c.f2  FROM t0 t   LEFT JOIN (SELECT t.sn,                  SUM(c.hsl) AS f1,                    decode(COUNT(C.CALCDATE),                            null,                            SUM(c.hsl)/COUNT(c.calcdate)) AS f2            FROM c            INNER JOIN t0 t ON (c.stkcode = t.stkcode                                AND c.mktcode = t.mktcode)            WHERE c.calcdate BETWEEN to_char(to_date(t.tdate,'yyyymmdd')-365,'yyyymmdd')AND s.tdate)            GROUP BY t.sn) c            ON (c.sn = t.sn);

先关联，再汇总WITH t0 AS (SELECT rownum AS sn,s.stkcode, t.mktcode,t.stype,t.sname,s.tdate FROM s,t WHERE s.stkcode = t.scode      AND t.status = 1      AND t.stype = 2      AND s.tdate >= to_number(to_char(SYSDATE -3,'YYYYMMDD')))SELECT t.stkcode,         t.mktcode,         t.stype,        t.sname,        t.tdate,        SUM(c.hsl) AS f1,        decode(COUNT(C.CALCDATE),0,null,SUM(c.hsl)/COUNT(c.calcdate))AS f2FROM TO tLEFT JOIN c ON (c.stkcode = t.stkcode AND c.mktcode = t.mktcode)WHERE c.calcdate BETWEE to_char(to_date(t.tdate,'yyyymmdd')-365,'yyyymmdd')                AND s.tdate)GROUP BY t.sn,t.skcode,t.mktcode,t.stype,t.sname,t,tdate;