select zyid00 ZYGHID,
       xm0000 BRXM00,
       xb0000 XB0000,
       rycwh0 CH0000,
       SF_BQ_CSRQTONL(csrq00) BRNL00,
       (select count(1)
          from JY_YW0000 a
         where a.ZYGHID = zy_brxxb0.zyid00
           and a.JGZT00 in ('0', '3', '1')
           and a.BBCYRQ is null
           and a.MZZYBZ = '1'
           and a.SQRQ00 >= '20210803'
           and a.SQRQ00 <= '20210803'
           and exists (select 1
                  from YJ_YW0000
                 where YJDJH0 = a.YJDJH0
                   and yjlrbz = 'N'
                   and nvl(HBZYXM, '0') <> '2')) wcj000,
       (select count(1)
          from JY_YW0000 a
         where a.ZYGHID = zy_brxxb0.zyid00
           and a.JGZT00 in ('1', '2', '6', '8')
           and a.BBCYRQ is not null
           and a.MZZYBZ = '1'
           and a.SQRQ00 >= '20210803'
           and a.SQRQ00 <= '20210803'
           and exists (select 1
                  from YJ_YW0000
                 where YJDJH0 = a.YJDJH0
                   and yjlrbz = 'N'
                   and nvl(HBZYXM, '0') <> '2')) ycj000,
       (select count(1)
          from JY_YW0000 a
         where a.ZYGHID = zy_brxxb0.zyid00
           and a.JGZT00 in ('3')
           and a.BBCYRQ is not null
           and a.MZZYBZ = '1'
           and a.SQRQ00 >= '20210803'
           and a.SQRQ00 <= '20210803'
           and exists (select 1
                  from YJ_YW0000
                 where YJDJH0 = a.YJDJH0
                   and yjlrbz = 'N')) yth000,
       (select count(1)
          from JY_YW0000 a
         where a.ZYGHID = zy_brxxb0.zyid00
           and a.MZZYBZ = '1'
           and exists (select 1
                  from YJ_YW0000
                 where YJDJH0 = a.YJDJH0
                   and JZBZ00 = 'Y')
           and a.SQRQ00 >= '20210803'
           and a.SQRQ00 <= '20210803'
           and exists (select 1
                  from YJ_YW0000
                 where YJDJH0 = a.YJDJH0
                   and yjlrbz = 'N')) jztj00
  from zy_brxxb0
 where zyid00 in
       (select ZYGHID
          from JY_YW0000 a
         where MZZYBZ = '1'
           and MZZYBZ = '1'
           and SQRQ00 >= '20210803'
           and SQRQ00 <= '20210803'
           and zyghid in (select zyid00
                            from zy_brxxb0
                           where zyid00 = a.zyghid
                             and dqbq00 = '2486'
                             and (sjcyrq is null or sjcyrq = '20991231' or
                                 brzt00 = '20'))
           and exists (select 1
                  from YJ_YW0000
                 where YJDJH0 = a.YJDJH0
                   and yjlrbz = 'N'
                   and nvl(HBZYXM, '0') <> '2'))
 order by CH0000