select distinct b.BRID00,a.ZYID00,a.BAH000,b.ZYH000,XM0000,b.XB0000,b.RYCWH0,b.CSRQ00,EXTRACT(year FROM sysdate) - EXTRACT(year FROM to_date(b.CSRQ00,'yyyyMMdd')) NL0000,b.BRZT00,b.RYRQ00,b.RYSJ00,a.RYRQ00 BLRYRQ,a.RYSJ00 BLRYSJ,b.CYDJRQ,b.CYDJSJ,CJYSBH,CJYSXM,A.CJRQ00,GDRQ00,XGGDRQ,JXBZ00,KFBZ00,a.YSZBH0,a.YSZMC0,e.SSKSBH as BMBH00,c.BMMC00,a.BLZT00,b.SJCYRQ,b.SJCYSJ,(select brdh00 from bm_brxxb0 g where g.brid00=a.brid00) LXDH00,b.SXYSXM,b.ZZYSXM,b.ZRYSXM,(select ZWXM00 from BM_YGBM00 where YGBH00=b.SZYS00) SZYSXM,trunc(to_date(decode(b.SJCYRQ,'20991231',to_char(sysdate,'yyyyMMdd'),b.SJCYRQ),'yyyyMMdd')-to_date(a.RYRQ00,'yyyyMMdd')) as ZYTS00,(select ZDMC00 from VW_DZBL_BRZDXX_ONE where ZYID00=a.ZYID00 and Rownum=1) XYZDMC,(select ZDBZ00 from VW_DZBL_BRZDXX_ONE where ZYID00=a.ZYID00 and Rownum=1) XYZDBZ,(select ZDMC00 from BQ_BRZDXX where ZYID00=a.ZYID00 and ZDLB00='a' and rownum<=1) ZYZDMC,(select ZDBZ00 from BQ_BRZDXX where ZYID00=a.ZYID00 and ZDLB00='a' and rownum<=1) ZYZDBZ,(select BMMC00 from BM_BMBM00 where BMBH00=b.DQBQ00 and rownum=1) BQMC00,(select ZDMC00 from (select ZYID00,ZDMC00 from BQ_BRZDXX t where ZDFS00=1 AND ZDLB00 = '2' order by plxh00) G where G.ZYID00=A.ZYID00 and ROWNUM <= 1)  RYZDMC,b.ybkh00,b.XB0000 as BRXB00,b.CSRQ00 as BRCSRQ ,b.BRRYCS, 
                (select ysxm00 from ZS_ZKLS00 where zyid00=a.zyid00 and rownum=1) ZKYSXM,
                (select CZRQ00 from ZS_ZKLS00 where zyid00=a.zyid00 and rownum=1) ZKRQ00,
                 (select CZSJ00 from ZS_ZKLS00 where zyid00=a.zyid00 and rownum=1) ZKSJ00
       ,(select c.brxx00 from bm_brxxb0 c where c.brid00=b.brid00)brxx00
        ,(select c.brjtdz from bm_brxxb0 c where c.brid00=b.brid00)brjtdz
        ,(select c.BRHYZK from bm_brxxb0 c where c.brid00=b.brid00)BRHYZK
        ,(select c.BRZY00 from bm_brxxb0 c where c.brid00=b.brid00)BRZY00
        ,(select c.BRMZ00 from bm_brxxb0 c where c.brid00=b.brid00)BRMZ00,b.DBZMC0,b.dbzbm0,(select sf_bq_csrqtonl(BRCSRQ) BRNL00 from bm_brxxb0 where brid00=a.brid00 ) BRNL00,(select ZDMC00 from VW_DZBL_BRZDXX_ONE where ZYID00=B.ZYID00 and rownum=1) ZDMC00
        ,(select c.BRGJ00 from bm_brxxb0 c where c.brid00=b.brid00)BRGJ00,decode(nvl((select value0 from xt_xtcs00  where name00='ZS_SFXSYWCLJ'),'Y'),'N',nvl((select max(decode(JSFS00,'',LJID00,0)) from LJ_ZRZC00 where ZYID00=A.ZYID00 ),0),nvl((select max(decode(JSFS00,'',LJID00,-1)) from LJ_ZRZC00 where ZYID00=A.ZYID00 and (nvl(JSRQ00,' ')=' ' or JSFS00 in('完成','强制退出'))),0)) DQLJID from ZS_BLXX01 a inner join ZY_BRXXB0 b on a.ZYID00=b.ZYID00 inner join ZS_YSZ000 e on a.YSZBH0=e.YSZID0 inner join BM_BMBM00 c on e.SSKSBH=c.BMBH00 and c.YYID00='C' left join ZS_ZKLS00 k on a.ZYID00=k.ZYID00 left join ZS_DPFKD0 D on A.ZYID00 = D.ZYID00 and D.ZT0000 in('1','2','3','4','6') where 1=1  and b.ZYH000 like '%00012%' order by BMBH00,YSZBH0,RYCWH0