CREATE OR REPLACE VIEW SD_HOSPITAL.VW_ZS13_YSXX00
 (
 YSXM00, YSBH00, YSLB00, YSLBMC, SSKSBH, SSKSMC, PYSM00, WBSM00, XKH000

 )
 AS 
select distinct A.YSXM00, A.YSBH00,D.YSLB00, (select C.YSLBMC from BM_YSLB00 C where D.YSLB00=C.YSLB00) YSLBMC ,T.SSKSBH,T.SSKSMC,D.PYSM00,D.WBSM00,D.XKH000
  from ZS_YSZ000 T, ZS_YSZMX0 A,BM_YGBM00 D,BM_BMBM00 E
 where T.YSZID0 = A.YSZID0 and A.YSBH00=D.YGBH00 and T.SSKSBH=E.BMBH00
   and T.YSZZT0 = '2'
   and E.BMXZ00='0'
   and E.CZBZ00 in ('0', '1')
   and A.YSZT00 = '2'
union all
select distinct C.ZWXM00,A.YGBH00,C.YSLB00,(select D.YSLBMC from BM_YSLB00 D where C.YSLB00=D.YSLB00) YSLBMC,A.BMBH00,B.BMMC00,C.PYSM00,C.WBSM00,C.XKH000
  from XT_YHBMQX A, BM_BMBM00 B,BM_YGBM00 C
 where A.BMBH00 = B.BMBH00 and A.YGBH00=C.YGBH00
   and B.BMXZ00 in ('1', '5','h')
   and B.CZBZ00 in ('0', '1')
   and A.XTDM00 = 'OE''
union all
 select distinct C.ZWXM00,A.YGBH00,C.YSLB00,(select D.YSLBMC from BM_YSLB00 D where C.YSLB00=D.YSLB00) YSLBMC,A.BMBH00,B.BMMC00,C.PYSM00,C.WBSM00,C.XKH000
  from XT_YHBMQX A, BM_BMBM00 B,BM_YGBM00 C
 where A.BMBH00 = B.BMBH00 and A.YGBH00=C.YGBH00
       and exists(select 1 from  BM_TYZD00 where ZDMC00='会诊医生' and BH0000=B.BMBH00 and MC0000=C.YGBH00 and  YXBZ00='1')
 union all
       select distinct C.ZWXM00,C.YGBH00,C.YSLB00,(select D.YSLBMC from BM_YSLB00 D where C.YSLB00=D.YSLB00) YSLBMC,A.BMBH00,A.BMMC00,C.PYSM00,C.WBSM00,C.XKH000
  from  BM_BMBM00 A,BM_YGBM00 C
 where A.BMBH00 = C.BMBH00 and exists(select 1 from  BM_TYZD00 where ZDMC00='会诊科室' and BH0000=A.BMBH00  and YXBZ00='1')
/