CREATE OR REPLACE VIEW SD_HOSPITAL.VW_YJ_GHF000_DJBZ
 (
 SFBZDJ, LBBH00, GHKS00, XMMC00, SFJE00, ZFJE00

 )
 AS 
select
  '0' as SFBZDJ,
  X.LBBH00,
  X.GHKS00,
  D.XMMC00,
  sum(C.SFJE00) SFJE00, 
  sum(C.SFJE00 - C.JMJE00) ZFJE00 
from  BM_GHXMDY X,BM_ZLZD00 A,XT_ZLSFGX B, BM_YYSFXM C,BM_FPXM00 D
where X.ZLXMID=A.ZLXMID and A.ZLXMID=B.ZLXMID and B.SFXMID=C.SFXMID and C.MZFPID=D.FPXMID and x.sybz00='1'
group by D.XMMC00, X.LBBH00, X.GHKS00
union all
select
  F.SFBZDJ,
  X.LBBH00,
  X.GHKS00,
  D.XMMC00,
  sum(F.DJSFJE) SFJE00, 
  sum(F.DJSFJE - C.JMJE00) ZFJE00 
from  BM_GHXMDY X,BM_ZLZD00 A,XT_ZLSFGX B, BM_YYSFXM C,BM_FPXM00 D,BM_SFDJBZ F
where X.ZLXMID=A.ZLXMID and A.ZLXMID=B.ZLXMID and B.SFXMID=C.SFXMID and C.MZFPID=D.FPXMID and C.SFXMID=F.SFXMID and F.SFBZDJ<>'0' and F.SFYP00 ='N'
group by D.XMMC00, X.LBBH00, X.GHKS00, F.SFBZDJ
/