select *from( select  a.*,d.bmmc00 bqmc00,c.bmmc00 ksmc00,(SELECT BMMC00 FROM BM_BMBM00 WHERE BMBH00=A.SQKS00)mzksmc,null sqjgmc, decode(a.zt0000,0,'申请入院',1,'申请通过',2,'未通知',3,'已通知',4,'登记入院',5,'已收治',6,'出院',7,'作废','未知状态') ztmc00,  a.tzyxrq||' '||a.tzyxsj tzyxrqsj, b.brxm00,  b.brlxdh,a.yyzyrq||' '||a.yyzysj yyzyrqsj,SF_BQ_CSRQTONL(B.BRCSRQ) NL0000, b.brxb00,a.yyczrq||' '||a.yyczsj yyczrqsj,a.tzrq00||' '||a.tzsj00 tzrqsj00,e.zwxm00 mzysxm,f.zwxm00 czyxm0,a.ysyjj0 from YS_BRZYSQ a,BM_BRXXB0 b,bm_bmbm00 C,bm_bmbm00 d,bm_ygbm00 e,bm_ygbm00 f   where a.BRID00=b.BRID00 and c.bmbh00 = a.KSH000 and a.tzczy0 = f.ygbh00(+)  and d.bmbh00 = a.bqh000 and a.ysgzh0 = e.ygbh00(+)  union all select  a.*,d.bmmc00 bqmc00,c.bmmc00 ksmc00,(SELECT BMMC00 FROM BM_BMBM00 WHERE BMBH00=A.SQKS00)mzksmc,b.jgmc00 sqjgmc, decode(a.zt0000,0,'申请入院',1,'申请通过',2,'未通知',3,'已通知',4,'登记入院',5,'已收治',6,'出院',7,'作废','未知状态') ztmc00,  a.tzyxrq||' '||a.tzyxsj tzyxrqsj, b.brxm00,  b.brdh00 brlxdh,a.yyzyrq||' '||a.yyzysj yyzyrqsj,SF_BQ_CSRQTONL(b.BRCSY0||lpad(b.BRCSM0,2,0)||lpad(b.BRCSD0,2,0)) NL0000, decode(b.XB0000,1,'男',2,'女') brxb00,a.yyczrq||' '||a.yyczsj yyczrqsj,a.tzrq00||' '||a.tzsj00 tzrqsj00,e.zwxm00 mzysxm,f.zwxm00 czyxm0,a.ysyjj0 from YS_BRZYSQ a,YS_BRZZB0 b,bm_bmbm00 C,bm_bmbm00 d,bm_ygbm00 e,bm_ygbm00 f  where a.ZYSQID=b.ZYSQID and c.bmbh00 = a.KSH000 and a.tzczy0 = f.ygbh00(+)  and d.bmbh00 = a.bqh000 and a.ysgzh0 = e.ygbh00(+) and a.brid00=0  ) where 1=1 and zt0000 in (1,2) and yyczrq >= '20181102'  and yyczrq <= '20181109'  and bqh000 = '2410'  order by yyczrq DESC,yyczsj DESC