SELECT B.LGTD,B.LTTD,A.STCD,B.STNM,B.ADDVCD,'ZZ' STTP,B.FRGRD,A.TM,A.Z VALUE FROM A, B
WHERE (NOT EXISTS(SELECT * FROM C WHERE STCD=A.STCD AND TM>A.TM AND TM>to_date('2011-08-11','yyyy-mm-dd') AND TM<=to_date('2011-08-14','yyyy-mm-dd')))
AND A.TM>to_date('2011-08-11','yyyy-mm-dd') AND TM<=to_date('2011-08-14','yyyy-mm-dd') AND A.STCD=B.STCD AND B.STTP IN('ZZ','ZQ','PP','DD') AND B.LGTD IS NOT NULL
AND B.LTTD IS NOT NULL AND ADDVCD IS NOT NULL;
现在有上面的一条SQL语句想进行优化，因为对于SQL不是太熟，只是听说子查询速度比较慢，所以想通过 join 的方式来处理，后来请同事写了一个，但效率并没有提高多少，所以想请大家帮忙看看，感谢～～
SELECT D.LGTD,D.LTTD,C.STCD,D.STNM,D.ADDVCD,'ZZ' STTP,D.FRGRD,C.TM,C.Z VAL
FROM
(select stcd,max(tm) maxtm from A
where tm > to_date('2011-08-11','yyyy-mm-dd') AND TM<=to_date('2011-08-14','yyyy-mm-dd')
group by stcd
) B
inner join C ON B.STCD = C.STCD
left join D ON C.STCD = D.STCD
where B.maxtm = C.TM and D.STTP IN('ZZ','ZQ','PP','DD') AND D.LGTD IS NOT NULL
AND D.LTTD IS NOT NULL AND D.ADDVCD IS NOT NULL;
这个是同事写的SQL～～
------解决方案--------------------
时间条件忘写了，呵呵
(select STCD,MAX(TM) AS TM,
max(Z)keep(dense_rank last order by TM) AS Z
FROM ST_RIVER_R
WHERE TM>to_date('2011-08-11','yyyy-mm-dd')
AND TM<=to_date('2011-08-14','yyyy-mm-dd')
GROUP BY STCD
) Z
------解决方案--------------------
ST_RIVER_R做全表扫描的成本较高
在这个表上建组合索引(STCD,TM,Z)，代替原有的(stcd,tm)
语句的话，将原来那条和楼上的写法来比较一下