该sql如何用简化，让其嵌套小于3层，并提高查询效率

Question

SELECT r.xm ,
    r.sfzjhm ,
    r.hjdxzqh,
    hjdxxdz ,
    r.csrq ,
    r.hyzk ,
    r.zfxm ,
    (SELECT COUNT(*) rzs FROM l_zn WHERE sfzjhm = r.sfzjhm AND xb = '1'
    ) rzs ,
    (SELECT COUNT(*) rzs FROM l_zn WHERE sfzjhm = r.sfzjhm AND xb = '2'
    ) nrs ,
    r.bzkdm ,
    (SELECT hyzcsx
    FROM
      (SELECT hyzcsx
      FROM l_rs
      WHERE fxhyrq IS NOT NULL
      AND sfzjhm    = r.sfzjhm
      AND rownum    = 1
      ORDER BY fxhyrq DESC
      )
    ) zcsx ,
    r.xjdxzqh,
    r.xjdxxdz ,
    r.ldzt ,
    r.jdsj ,
    r.lrddjrq ,
    r.lcrq ,
    (SELECT zs
    FROM
      (SELECT floor((sysdate-fxhyrq)/7) zs
      FROM l_rs
      WHERE fxhyrq IS NOT NULL
      AND rsjg NOT IN ('10','11','12','20','21','22','23','30','40','90')
      AND sfzjhm    = r.sfzjhm
      AND rownum    = 1
      ORDER BY fxhyrq DESC
      )
    ) hyzs ,
    r.hkxz ,
    r.zmblzt
  FROM l_ldry r

Answer 1

order by 拿出來看看啊 

Answer 2

l_lfry表结构
RYBM VARCHAR2(23 BYTE) Yes
SFZJHM VARCHAR2(30 BYTE) Yes
XM VARCHAR2(100 BYTE) Yes
CSRQ DATE Yes
XB VARCHAR2(1 BYTE) Yes
HKXZ VARCHAR2(3 BYTE) Yes
MZ VARCHAR2(3 BYTE) Yes
HYZK VARCHAR2(3 BYTE) Yes
WHCD VARCHAR2(3 BYTE) Yes
ZFRYBM VARCHAR2(23 BYTE) Yes
ZFSFZJLX VARCHAR2(2 BYTE) Yes
FZSFZJHM VARCHAR2(30 BYTE) Yes
ZFXM VARCHAR2(100 BYTE) Yes
ZFCSRQ DATE Yes
ZFHJDDM VARCHAR2(12 BYTE) Yes
ZFHKXZ VARCHAR2(1 BYTE) Yes
ZFMZ VARCHAR2(3 BYTE) Yes
ZFHYZK VARCHAR2(2 BYTE) Yes
ZFWHCD VARCHAR2(3 BYTE) Yes
ZMBLZT VARCHAR2(1 BYTE) Yes
HYZMHM VARCHAR2(20 BYTE) Yes
SFLDSZ VARCHAR2(1 BYTE) Yes
DQBYZK VARCHAR2(30 BYTE) Yes
BZKDM VARCHAR2(3 BYTE) Yes
HYDJRQ DATE Yes
HYFZJG VARCHAR2(60 BYTE) Yes
HJDXZQH VARCHAR2(12 BYTE) Yes
HJDXXDZ VARCHAR2(200 BYTE) Yes
HJDYZBM CHAR(6 BYTE) Yes
XJDXZQH VARCHAR2(80 BYTE) Yes
XJDXXDZ VARCHAR2(200 BYTE) Yes
XJZDYZBM CHAR(6 BYTE) Yes
LCRQ DATE Yes
FHRQ DATE Yes
LRDDJRQ DATE Yes
LRDTCRQ DATE Yes
LDLX VARCHAR2(1 BYTE) Yes
LDZT VARCHAR2(1 BYTE) Yes
SFTC VARCHAR2(1 BYTE) Yes
TCYY VARCHAR2(1 BYTE) Yes
SFBSGL VARCHAR2(1 BYTE) Yes
DJDW VARCHAR2(12 BYTE) Yes
LDYY VARCHAR2(1 BYTE) Yes
JDSJ DATE Yes
l_zn表结构
ID VARCHAR2(10 BYTE) No
SFZJHM VARCHAR2(30 BYTE) Yes
XM VARCHAR2(30 BYTE) Yes
CSRQ DATE Yes
XB VARCHAR2(1 BYTE) Yes
HC VARCHAR2(1 BYTE) Yes
XYGX VARCHAR2(1 BYTE) Yes
DQJKZK VARCHAR2(2 BYTE) Yes
ZCSX VARCHAR2(1 BYTE) Yes
FUKSRQ DATE Yes
FYJSRQ DATE Yes
FYJSYY VARCHAR2(1 BYTE) Yes
ZNRYBM VARCHAR2(23 BYTE) Yes
l_rs表结构
ID VARCHAR2(10 BYTE) No
SFZJHM VARCHAR2(18 BYTE) Yes
FXHYRQ DATE Yes
FXHYZS VARCHAR2(3 BYTE) Yes
MCYJRQ DATE Yes
HYZCSX VARCHAR2(1 BYTE) Yes
RSJG VARCHAR2(2 BYTE) Yes
JCRQ DATE Yes
CSRQ DATE Yes

Answer 3

写错了 是L_ldry 不是l_lfry

Answer 4

你的效率问题是嵌套引起的？看了你的嵌套也不是很多啊， (SELECT COUNT(*) rzs FROM l_zn WHERE sfzjhm = r.sfzjhm AND xb = '1'
  ) rzs ,
  (SELECT COUNT(*) rzs FROM l_zn WHERE sfzjhm = r.sfzjhm AND xb = '2'
  ) nrs ,可以合并，按照1,2分组统计即可先看看执行计划吧，哪里慢吧

Answer 5

2个rownum位置应该错了    (SELECT hyzcsx
  FROM
     (SELECT hyzcsx
      FROM l_rs
      WHERE fxhyrq IS NOT NULL
            AND sfzjhm = r.sfzjhm
      ORDER BY fxhyrq DESC
  ) where rownum = 1
  ) zcsx , (SELECT zs
  FROM
      (SELECT floor((sysdate-fxhyrq)/7) zs
       FROM l_rs
       WHERE fxhyrq IS NOT NULL
             AND rsjg NOT IN ('10','11','12','20','21','22','23','30','40','90')
             AND sfzjhm = r.sfzjhm
       ORDER BY fxhyrq DESC
  ) where rownum = 1
  ) hyzs这表的数据有多少？执行时间大概多少？

Answer 6

300W   花费了14秒   (SELECT COUNT(*) rzs FROM l_zn WHERE sfzjhm = r.sfzjhm AND xb = '1'
  ) rzs ,
  (SELECT COUNT(*) rzs FROM l_zn WHERE sfzjhm = r.sfzjhm AND xb = '2'
  ) nrs ,
SELECT zs
  FROM
      (SELECT floor((sysdate-fxhyrq)/7) zs
       FROM l_rs
       WHERE fxhyrq IS NOT NULL
             AND rsjg NOT IN ('10','11','12','20','21','22','23','30','40','90')
             AND sfzjhm = r.sfzjhm
       ORDER BY fxhyrq DESC
  ) where rownum = 1
  ) hyzs这三部分 效率太低   还有就是 最里层的L_ldry r的别名r不起作用      以前在10g 32位上还起作用  现在10g64位居然不起作用

Answer 7

with tb1 as(SELECT sfzjhm rzs FROM l_zn WHERE xb = '1') ,
tb2 as(SELECT sfzjhm rzs FROM l_zn WHERE  xb = '2') ,
tb3 as (SELECT floor((sysdate-fxhyrq)/7) zs,sfzjhm 
  FROM l_rs
  WHERE fxhyrq IS NOT NULL
  AND rsjg NOT IN ('10','11','12','20','21','22','23','30','40','90')
  ORDER BY fxhyrq DESC)
(select count(1) from tb1 where sfzjhm = r.sfzjhm) rzs ,
(select count(1) from tb2 where sfzjhm = r.sfzjhm) nrs ,
(select zs from(select zs from tb3 where sfzjhm = r.sfzjhm)where rownum=1) hyzs