select B.BRID00,
       A.ZYID00,
       decode((select count(1)
                from LJ_ZRZC00
               where nvl(jsfs00, ' ') = ' '
                 and zyid00 = a.zyid00),
              0,
              '0',
              '1') sfljbr,
       nvl((select max(decode(JSFS00, '', LJID00, -1))
             from LJ_ZRZC00
            where ZYID00 = A.ZYID00
              and (nvl(JSRQ00, ' ') = ' ' or JSFS00 in ('完成', '强制退出'))),
           0) as DQLJID,
       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,
       decode(a.cyrq00, '20991231', b.sjcyrq, a.cyrq00) CYRQ00,
       decode(a.cySJ00, '23:59:59', b.sjcySJ, a.cySJ00) CYSJ00,
       B.SXYSXM,
       B.ZZYSXM,
       B.ZRYSXM,
       a.CJYSBH,
       a.CJYSXM,
       a.CJRQ00,
       a.JXBZ00,
       a.KFBZ00,
       A.YSZBH0,
       A.YSZMC0,
       e.SSKSBH as BMBH00,
       C.BMMC00,
       A.BLZT00,
       k.zkysmc as ZKYSXM,
       k.zkrq00 as ZKRQ00,
       '' as ZKSJ00,
       (select BMMC00
          from BM_BMBM00
         where BMBH00 = b.DQBQ00
           and rownum = 1) BQMC00,
       (select ZDMC00
          from BQ_BRZDXX
         where ZDLB00 = '3'
           and ZYID00 = a.ZYID00
           and Rownum = 1) ZDMC00,
       (select ZDMC00
          from BQ_BRZDXX
         where ZYID00 = A.ZYID00
           and ZDLB00 = 'a'
           and rownum <= 1) ZYZDMC,
       f.ZT0000 JKZT00,
       f.CZYXM0 JKRXM0,
       f.KSRQ00 JKKSRQ,
       f.KSSJ00 JKKSSJ,
       GDRQ00,
       XGGDRQ,
       trunc(to_date(decode(B.SJCYRQ,
                            '20991231',
                            to_char(sysdate, 'yyyyMMdd'),
                            B.SJCYRQ),
                     'yyyyMMdd') - to_date(A.RYRQ00, 'yyyyMMdd')) as ZYTS00,
       B.RYLB00,
       (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,
       DBZBM0,
       DBZMC0,
       B.BRRYCS,
       B.ZRZYBZ,
       (select z.ssm000
          from ba_brssxx z
         where z.lsh000 = a.zyid00
           and z.SSLB00 = '1'
           and rownum = 1) ssm000,
       (select z.ssmc00
          from ba_brssxx z
         where z.lsh000 = a.zyid00
           and z.SSLB00 = '1'
           and rownum = 1) ssmc00,
       (select zdm000
          from BQ_BRZDXX m
         where m.zyid00 = a.zyid00
           and m.zdlb00 = '3'
           and rownum = 1) zdm000,
       (select ZWXM00 from BM_YGBM00 where YGBH00 = B.SZYS00) SZYS00
  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
  left join ba_brzyxx k on a.ZYID00 = k.LSH000
  left join ZS_ZDJKBL f on a.ZYID00 = f.ZYID00
 where BLZT00 <> '3'
   and c.YYID00 = '221956'
   and decode(a.cyrq00, '20991231', b.sjcyrq, a.cyrq00) >= '20200101'
   and decode(a.cyrq00, '20991231', b.sjcyrq, a.cyrq00) <= '20200131'
   and BLZT00 <> '4'
   and (BRZT00 like '3%' or BRZT00 like '4%' or BRZT00 like '5%')
 order by BMBH00, YSZBH0, RYCWH0