select distinct t1.hzbs00,t1.hzbs01,t5.jzrq00,case when t4.mzh000 is null then t1.mzh000 else t4.mzh000 end mzh000,t1.hzxm00,t1.hzxb00,t1.hznl00,t1.hznl00 nl0000,t1.djksmc,t1.zlfamc,t1.djrxm0,t1.zxzt00 ,t1.kh0000,
case when t2.yycs00 is null then 0 else  t2.yycs00 end yycs00,case when t3.zlcs00 is null then 0 else t3.zlcs00 end zlcs00,to_char(t4.zlkssj,'yyyy-mm-dd hh24:mi:ss')  zlkssj,t1.id0000  ,case when  t1.sfqy00 is null then '1'  else t1.sfqy00 end sfqy00 ,case when (select count(1)  from zl_zlyyb0 a  where FADJID =t1.id0000 and  yyrq00 >to_char(sysdate,'yyyyMMdd')  and not exists ( select 1 from zl_lxpjb0  where a.fadjid = fadjid ) ) in(1,2 ) then '1' else '' end sfxs00   from 
(select case when a.hzbs00=1 then '门诊' else '住院' end hzbs00, a.hzbs00 hzbs01,a.mzh000,a.hzxm00,a.hzxb00,a.hznl00,a.djksmc,b.zlfamc,a.id0000 ,b.zlfabh,a.djrxm0,a.kh0000,a.sfqy00 ,
case when a.tzczsj is not null then '已停止' else '执行中' end zxzt00 
 from zl_fadj00 a, zl_zlfa00 b where a.zlfaid =b.id0000  and a.sfsc00=1 

       and  a.hzbs00= '1'
) t1 ,
(select fadjid,sum(yycs00) yycs00 from (select case when regexp_substr(yysd00, '[^,]+', 1, 2) is null then 1 else 2 end yycs00,fadjid from zl_zlyyb0 ) group by fadjid) t2 ,  
( select fadjid,count(1) zlcs00 from (select fadjid,dd,sd,count(1) yzls00 from (select fadjid, to_char(zlkssj,'yyyymmdd') dd,to_char(zlkssj,'hh24'),
CASE  WHEN to_char(zlkssj,'hh24') BETWEEN 0 AND 11 THEN 'am' else 'pm' END sd from zl_zljlb0 where sfsc00=1)
group by fadjid,dd,sd) group by fadjid) t3,
 (select fadjid,mzh000,zlkssj,id0000 from zl_zljlb0 where sfsc00=1
 and to_char(zlkssj ,'yyyymmdd')>='20190101'
       and to_char(zlkssj ,'yyyymmdd')<='20221118'
) t4,
   (select ghid00,ghh000, xm0000,jzks00,jzysxm,jzrq00,brid00,jzys00,KDRQ00 from sd_hospital.VW_ZL_JRJZLB
   where 1=1 
        and  jzrq00 between '20190101' and '20221118'
     )t5
 where t1.id0000=t2.fadjid(+) and t1.id0000=t3.fadjid(+) and  t1.id0000=t4.fadjid
 and t4.mzh000=t5.ghh000 
 and to_char(t4.zlkssj,'yyyymmdd') =t5.jzrq00
 order by  zlkssj  desc