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 >= '20170213'
           and a.SQRQ00 <= '20170213'
           and exists (select 1
                  from YJ_YW0000
                 where YJDJH0 = a.YJDJH0
                   and yjlrbz = 'N')) 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 >= '20170213'
           and a.SQRQ00 <= '20170213'
           and exists (select 1
                  from YJ_YW0000
                 where YJDJH0 = a.YJDJH0
                   and yjlrbz = 'N')) 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 >= '20170213'
           and a.SQRQ00 <= '20170213'
           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 JZBZ00 from YJ_YW0000 where YJDJH0 = a.YJDJH0)
           and a.SQRQ00 >= '20170213'
           and a.SQRQ00 <= '20170213'
           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 >= '20170213'
                     and SQRQ00 <= '20170213'
                     and zyghid in
                         (select zyid00
                            from zy_brxxb0
                           where zyid00 = a.zyghid
                             and dqbq00 = '48'
                             and (sjcyrq is null or sjcyrq = '20991231'))
                     and exists (select 1
                            from YJ_YW0000
                           where YJDJH0 = a.YJDJH0
                             and yjlrbz = 'N')
                     and exists (select 1
                            from BM_ZLZD00
                           where ZLXMID = a.ZLXMID
                             and lbbh00 = 4))
 order by CH0000