select d.BRID00,
       d.BRID00,
       b.ZYID00,
       b.SWRQ00,
       d.ZYH000,
       d.MRN000,
       d.XM0000,
       d.XB0000,
       b.YSZBH0,
       b.YSZMC0,
       e.SSKSBH,
       c.BMMC00,
       b.BLZT00,
       d.RYRQ00,
       d.RYSJ00,
       b.RYRQ00 BLRYRQ,
       b.RYSJ00 BLRYSJ,
       d.SJCYRQ,
       d.SJCYSJ,
       d.RYCWH0,
       k.zkysmc as ZKYSXM,
       k.zkrq00 as ZKRQ00,
       '' as ZKSJ00,
       d.SXYSXM,
       d.ZZYSXM,
       d.ZRYSXM,
       (select ZDMC00
          from VW_DZBL_BRZDXX_ONE
         where ZYID00 = b.ZYID00
           and Rownum = 1) ZDMC00,
       (select BMMC00
          from BM_BMBM00
         where BMBH00 = d.DQBQ00
           and rownum = 1) BQMC00,
       SF_BQ_CSRQTONL(d.CSRQ00) BRNL00,
       decode((select count(1)
                from LJ_ZRZC00
               where nvl(jsfs00, ' ') = ' '
                 and zyid00 = d.zyid00),
              0,
              '0',
              '1') sfljbr,
       nvl((select max(decode(JSFS00, '', LJID00, -1))
             from LJ_ZRZC00
            where ZYID00 = d.ZYID00
              and (nvl(JSRQ00, ' ') = ' ' or JSFS00 in ('完成', '强制退出'))),
           0) as DQLJID,
       TRUNC(to_date(DECODE(d.SJCYRQ,
                            '20991231',
                            to_char(sysdate, 'yyyyMMdd'),
                            d.SJCYRQ),
                     'yyyyMMdd') - to_date(B.RYRQ00, 'yyyyMMdd')) as ZYTS00,
       GDRQ00,
       XGGDRQ,
       (select decode(sign(count(1)), '1', '是', '否')
          from ZS_BLWS00
         where WSZT00 <> '3'
           and BLXMBH = '34'
           and ZYID00 = B.ZYID00) YNBLTL, --疑难病例讨论
       (select decode(sign(count(1)), '1', '是', '否')
          from ZS_BLWS00
         where WSZT00 <> '3'
           and BLXMBH = '42'
           and ZYID00 = B.ZYID00) SWBLTL, --死亡病例讨论 
       D.DBZMC0,
       (select ZDMC00
          from VW_DZBL_BRZDXX_ONE
         where ZYID00 = b.ZYID00
           and zdlb00 = '3'
           and rownum = 1) CYZYZD,
       (select ZDMC00
          from VW_DZBL_BRZDXX_ONE
         where ZYID00 = b.ZYID00
           and zdlb00 = 'a'
           and rownum = 1) CYZYJB
  from ZS_BLXX01 b
 inner join ZY_BRXXB0 d on b.ZYID00 = d.ZYID00
 inner join ZS_YSZ000 e on b.YSZBH0 = e.YSZID0
 inner join BM_BMBM00 c on e.SSKSBH = c.BMBH00
  left join ba_brzyxx k on a.ZYID00 = k.LSH000
 where 1 = 1
   and c.YYID00 = '220006'
   and b.ZYID00 in
       (select a1.LSH000 as ZYID00
          from BA_BRZYXX a1
         inner join ZS_BLXX01 b1 on a1.LSH000 = b1.ZYID00
         where (a1.LYFS00 = '死亡' or a1.LYFS00 = '5')
           and a1.CYRQ00 >= '20180628'
           and a1.CYRQ00 <= '20180928'
        union all (select a1.ZYID00
                    from ZS_BLXX01 a1
                    left join ZS_SWBGK0 a2 on a1.zyid00 = a2.zyid00
                   where 1 = 1
                     and a1.SWRQ00 >= '20180628'
                     and a1.SWRQ00 <= '20180928')
        union all (select a1.ZYID00
                    from ZY_BRXXB0 a1
                   inner join ZS_BLXX01 b1 on a1.ZYID00 = b1.ZYID00
                    left join BQ_BRZDXX c1 on a1.ZYID00 = c1.ZYID00
                                          and c1.ZDLB00 = '2'
                   inner join ZS_BLWS00 d1 on a1.ZYID00 = d1.ZYID00
                                          and d1.BLXMBH = '41'
                                          and WSZT00 <> '3'
                   where 1 = 1
                     and a1.SJCYRQ >= '20180628'
                     and a1.SJCYRQ <= '20180928'))
 order by RYCWH0