SQL优化-利用with as的方式去掉nestloop连接

原语句：

select substr(i.disbdt, 1, 6),
       '20150228',
       nvl(sum(j.onlnbl), 0),
       nvl(sum(k.orgibl) - sum(k.onlnbl), 0) + nvl(sum(m.tranam), 0),
       nvl(sum(l1.onlnbl), 0),
       nvl(sum(l2.onlnbl), 0),
       nvl(sum(l3.onlnbl), 0),
       nvl(sum(l4.onlnbl), 0),
       nvl(sum(l6.onlnbl), 0),
       nvl(sum(l6.onlnbl), 0),
       nvl(sum(l7.onlnbl), 0),
       nvl(sum(l8.orgibl) - sum(l8.onlnbl), 0),
       nvl(sum(m.tranam), 0)
  from tmp_mtlncf h
  join srcapp.lnb_lncf i
    on h.lncfno = i.lncfno
  left join temp_cr_bl j
    on i.acctid = j.acctid
   and j.transt = '1'
  left join temp_cr_bl k
    on i.acctid = k.acctid
   and k.lnbltp in ('2', 'A')
  left join temp_cr_bl l1
    on i.acctid = l1.acctid
   and l1.transt = '1'
   and l1.lnbltp = '2'
   and To_Date('20150228', 'yyyymmdd') - l1.inptdt between 1 and 30
  left join temp_cr_bl l2
    on i.acctid = l2.acctid
   and l2.transt = '1'
   and l2.lnbltp = '2'
   and To_Date('20150228', 'yyyymmdd') - l2.inptdt between 31 and 60
  left join temp_cr_bl l3
    on i.acctid = l3.acctid
   and l3.transt = '1'
   and l3.lnbltp = '2'
   and To_Date('20150228', 'yyyymmdd') - l3.inptdt between 61 and 90
  left join temp_cr_bl l4
    on i.acctid = l4.acctid
   and l4.transt = '1'
   and l4.lnbltp = '2'
   and To_Date('20150228', 'yyyymmdd') - l4.inptdt between 91 and 120
  left join temp_cr_bl l5
    on i.acctid = l5.acctid
   and l5.transt = '1'
   and l5.lnbltp = '2'
   and To_Date('20150228', 'yyyymmdd') - l5.inptdt between 121 and 150
  left join temp_cr_bl l6
    on i.acctid = l6.acctid
   and l6.transt = '1'
   and l6.lnbltp = '2'
   and To_Date('20150228', 'yyyymmdd') - l6.inptdt between 151 and 180
  left join temp_cr_bl l7
    on i.acctid = l7.acctid
   and l7.transt = '1'
   and l7.lnbltp = '2'
   and To_Date('20150228', 'yyyymmdd') - l7.inptdt > 180
  left join temp_cr_bl l8
    on i.acctid = l8.acctid
   and l8.lnbltp in ('2', 'A')
   and l8.inp