1、rownum

(1)

较慢SELECT ywlsdm, qybm, spzt, czsj, czrydm FROM GH_hs WHERE ywlsdm in (SELECT ywlsdm FROM GH_hs WHERE spzt = ‘2‘)
较快SELECT ywlsdm, qybm, spzt, czsj, czrydm FROM GH_hs WHERE ywlsdm in (SELECT ywlsdm FROM GH_hs WHERE spzt = ‘2‘ and rownum <= (SELECT COUNT(*) FROM GH_hs hs WHERE EXISTS (SELECT ywlsdm FROM GH_hs WHERE spzt = ‘2‘)))

2、with as 临时表

select * from (with qybm as (select gx.qybm from (select a.ghdm from gh_ghdm a start with a.ghdm = ‘1‘ connect by prior a.ghdm = a.sjghdm) gh inner join gh_qyghgx gx on gh.ghdm = gx.tjghdm ),yg as ( select * from gh_jc_yg t where t.yglx = ‘0‘)select yg.* from qybm , yg where qybm.qybm = yg.qyghdm )