SELECT 病历状态,病案状态,出院科室,住院号,姓名,主治医生,主任医生,住院医生,入院日期,入院时间,出院日期,出院时间 FROM (
select
1 XH, 
decode(BLZT00,'1','正常','2','转入','3','归档','4','删除','5','退回') 病历状态,
decode(nvl(C.SHBZ00,'9'),'9','未创建','0','未提交','1','待签收','2','已审核','3','退回','4',
          '已修订','5','已签收','未提交') ||  
    decode(A.SDBASY,'1','(已锁)','') 病案状态,
decode(cykbmc,'',(SELECT BMMC00 FROM bm_bmbm00 WHERE BMBH00=B.DQKS00),cykbmc) 出院科室,
b.zyh000 住院号,
b.XM0000 姓名,
b.zzysxm 主治医生,
b.zrysxm 主任医生,
b.sxysxm 住院医生,
nvl(c.RYRQ00,a.RYRQ00) 入院日期,
nvl(c.RYSJ00,a.RYSJ00) 入院时间,
nvl(c.cyrq00,a.CYRQ00) 出院日期,
nvl(c.CYSJ00,a.CYSJ00) 出院时间
from zs_blxx01 a,zy_brxxb0 b,ba_brzyxx c where a.zyid00=b.zyid00 and b.zyid00=c.lsh000(+) and nvl(c.cyrq00,a.CYRQ00) between '20201201' and '20201230'
and nvl(c.cykb00,B.DQKS00) in(select bmbh00 from bm_bmbm00 where yyid00 in('A','B','D'))
UNION ALL
select
2 XH,'总计：', TO_CHAR(COUNT(*)),
'未创建：', TO_CHAR(NVL(SUM(DECODE(C.SHBZ00,'',1)),'0')),'','','','','','','',''
from zs_blxx01 a,zy_brxxb0 b,ba_brzyxx c where a.zyid00=b.zyid00 and b.zyid00=c.lsh000(+) and nvl(c.cyrq00,a.CYRQ00) between '20201201' and '20201230'
and nvl(c.cykb00,B.DQKS00) in(select bmbh00 from bm_bmbm00 where yyid00 in('A','B','D'))
)ORDER BY XH,出院日期;