select --A.BMBH00,
       A.BMMC00 AS 科室名称,
       YNHZS0 AS 疑难危重急症,--急症、危重、疑难患者数
       ZYHZS0 AS 中医类收费的患者数 ,--中医类收费的患者数 
       decode(A.YNHZS0,0,'0.00',decode(A.ZYHZS0,0,'0.00',ROUND(A.ZYHZS0/A.YNHZS0* 100,2)))||'%' AS 急症危重疑难患者中医参与治疗率,--急症、危重、疑难患者中医参与治疗率
       YZHZS0 AS 急症患者数,---急症患者数
       wzhzs0 AS 危重患者数,---危重患者数
       YNBRSL AS 疑难患者数, --疑难患者数
       JZWZ00 AS 急症危重患者数, --急症危重患者数
       JZYN00 AS 急症疑难患者数, --急症疑难患者数
       WZYN00 AS 危重疑难患者数
  from(
  select A.BMBH00,
       A.BMMC00,
       (select count(distinct A.LSH000)
      from BA_BRZYXX A
     where A.BMBH00=A.CYKB00    and (A.CYRQ00>='20200419'and A.CYRQ00<='20200519')
       and (exists (select 1
                      from BA_BRZYFY H
                     where (H.SFJZ00 = '1' or H.SFYNBL = '1')
                       and H.ZYID00 = A.LSH000) or exists
            (select 1
               from BQ_YJYZ00 H
              where H.YZZT00 <> '4'
                and (H.ZLXMJC like '%病危通知%' or H.ZLXMJC like '%病重通知%')
                and H.ZYID00 = A.LSH000))  ) YNHZS0,--急症、危重、疑难患者数   
    (select count(distinct A.LSH000)
      from BA_BRZYXX A
     where A.BMBH00=A.CYKB00    and (A.CYRQ00>='20200419'and A.CYRQ00<='20200519')
       and exists (select 1
              from ZY_BRFY00 H1, ZY_FYMX00 H2, BM_YYSFXM H3, BM_BAXM00 H4
             where H1.DJH000 = H2.DJH000
               and H2.XMBH00 = H3.SFXMID
               and H3.BAXMID = H4.BAXMID
               and H4.BH0000 || '' like '05%'
               and A.LSH000=H1.ZYID00)--中医类收费的患者数 
       and (exists (select 1
                      from BA_BRZYFY H
                     where (H.SFJZ00 = '1' or H.SFYNBL = '1')
                       and H.ZYID00 = A.LSH000) or exists
            (select 1
               from BQ_YJYZ00 H
              where H.YZZT00 <> '4'
                and (H.ZLXMJC like '%病危通知%' or H.ZLXMJC like '%病重通知%')
                and H.ZYID00 = A.LSH000))  ) ZYHZS0,--中医类收费的急症、危重、疑难患者数  
                ---急症---
                (select count(distinct A.LSH000)
      from BA_BRZYXX A
     where A.BMBH00=A.CYKB00    and (A.CYRQ00>='20200419'and A.CYRQ00<='20200519')
       and exists (select 1
                      from BA_BRZYFY H
                     where H.SFJZ00 = '1'
                       and H.ZYID00 = A.LSH000) ) YZHZS0,--急症患者数  
                       ---- 
                       (select count(distinct A.LSH000)
      from BA_BRZYXX A
     where A.BMBH00=A.CYKB00    and (A.CYRQ00>='20200419'and A.CYRQ00<='20200519')
       and exists(select 1
               from BQ_YJYZ00 H
              where H.YZZT00 <> '4'
                and (H.ZLXMJC like '%病危通知%' or H.ZLXMJC like '%病重通知%')
                and H.ZYID00 = A.LSH000)) wzhzs0,--危重患者数 
                --疑难
                 (select count(distinct A.LSH000)
      from BA_BRZYXX A
     where A.BMBH00=A.CYKB00    and (A.CYRQ00>='20200419'and A.CYRQ00<='20200519')
       and exists (select 1
                      from BA_BRZYFY H
                     where H.SFYNBL = '1'
                       and H.ZYID00 = A.LSH000) ) YNBRSL,--疑难患者数  
                       ---急诊危重
                       (select count(distinct A.LSH000)
      from BA_BRZYXX A
     where A.BMBH00=A.CYKB00    and (A.CYRQ00>='20200419'and A.CYRQ00<='20200519')
       and (exists (select 1
                      from BA_BRZYFY H
                     where H.SFJZ00 = '1'
                       and H.ZYID00 = A.LSH000) or exists
            (select 1
               from BQ_YJYZ00 H
              where H.YZZT00 <> '4'
                and (H.ZLXMJC like '%病危通知%' or H.ZLXMJC like '%病重通知%')
                and H.ZYID00 = A.LSH000))  ) JZWZ00,--急症危重患者数 
                --- 急症疑难
                 (select count(distinct A.LSH000)
      from BA_BRZYXX A
     where A.BMBH00=A.CYKB00    and (A.CYRQ00>='20200419'and A.CYRQ00<='20200519')
       and exists (select 1
                      from BA_BRZYFY H
                     where (H.SFJZ00 = '1' or H.SFYNBL = '1')
                       and H.ZYID00 = A.LSH000) 
              ) JZYN00,--急症疑难
              --危重疑难
   (select count(distinct A.LSH000)
      from BA_BRZYXX A
     where A.BMBH00=A.CYKB00    and (A.CYRQ00>='20200419'and A.CYRQ00<='20200519')
       and (exists (select 1
                      from BA_BRZYFY H
                     where H.SFYNBL = '1'
                       and H.ZYID00 = A.LSH000) or exists
            (select 1
               from BQ_YJYZ00 H
              where H.YZZT00 <> '4'
                and (H.ZLXMJC like '%病危通知%' or H.ZLXMJC like '%病重通知%')
                and H.ZYID00 = A.LSH000))  ) WZYN00--危重、疑难患者数                            
  from BM_BMBM00 A
  where A.FLAG00 = '1'
   and A.BMXZ00 in ('0', '9')
   and exists (select SSKSBH from ZS_YSZ000 where SSKSBH = A.BMBH00) AND CZBZ00<>'2'
  order by BMBH00) A