【Sql语句】自己摸索出得比较复杂的一个查询语句

   select t7.cllx,t8.clmc,   
        case when exists (select 1 from zs_xtgl_bgjb t9 where t1.id = t9.fxid) then '1' else '0' end sfbgjb,
case when exists (select 1 from zs_wsbm_bmfxb t4 where t1.id = t4.fxid and t4.yhid=#{yhid}) then '1' else '0' end bmzt
from zs_xtgl_ndfxb t1
left join zs_xtgl_fxdmb t2 on t1.fxid = t2.id
left join zftal_xtgl_jcsjb t3 on t2.xlcc = t3.dm and t3.lx='zsxt_xlcc'
left join zftal_xtgl_zydmb t6 on t2.zydm = t6.zydm_id
left join zftal_xtgl_bmdmb t7 on t6.bmdm_id_lsbm = t7.bmdm_id
left join (select wm_concat(distinct(t.cllx)), wm_concat(distinct(b.mc)) clmc,bmdm_id
  from (select ltrim(regexp_substr(a.cllx, '[^,]+', 1, level, 'i')) cllx,bmdm_id
          from (select cllx, bmdm_id from zftal_xtgl_bmdmb) a
        connect by level <=
                   length(a.cllx) - length(regexp_replace(a.cllx, ',', '')) + 1) t
  left join zftal_xtgl_jcsjb b
    on t.cllx = b.dm
   and b.lx = 'zsxt_cllx' group by t.bmdm_id) t8 on t7.bmdm_id = t8.bmdm_id
where t2.bmlx = #{lxdm} and exists (
select 1 from zs_xtgl_csszb t5 where t1.zsnd = t5.zsnd
) and not exists (
   select 1 from zs_xtgl_bgjb t8 where t1.id = t8.jbfx

)

注：第一次用regexp_substr函数，摸索了半天，仅供参考