CREATE OR REPLACE VIEW SD_HOSPITAL.VW_YS_FBFYYZ
 (
 ZLXMID, LBBH00, ZLXMJC, ZLXMBH, ZYZBID, PYSM00, WBSM00, BH0000, DW0000, 
FBBH00, YBZXLB, YBLBMC, SFJE00, ZFBL00, YBBRLB, SFSFXM

 )
 AS 
select 
      a.ZLXMID,--项目ID
      a.LBBH00,--类别编号
      a.ZLXMJC,--项目简称
      a.ZLXMBH,--诊疗项目编号
      a.ZYZBID,--专业组别
      a.PYSM00,--拼音首码
      a.WBSM00,--五笔首码
      a.BH0000,--辅助编号
      a.DW0000,--单位
      1 FBBH00,
      '0' YBZXLB,--医保中心类别
      '自付' YBLBMC,
      sum(NVL(C.TCJE00,b.SFJE00)*c.SFCS00) SFJE00,--单价
      '100%' ZFBL00,--自付比例
      '0' YBBRLB,--医保病人类别
      'Y' SFSFXM --是否收费项目
from  VW_YS_YXSQZL a, VW_BM_YYSFXM b, XT_ZLSFGX c
where c.ZLXMID = a.ZLXMID and b.SFXMID = c.SFXMID and c.GDBZ00='Y'      
group by a.ZLXMID,a.LBBH00,a.ZYZBID,a.ZLXMJC,a.ZLXMBH,a.PYSM00,a.WBSM00,a.BH0000,a.DW0000
Union all select
      ZLXMID,--项目ID
      LBBH00,--类别编号
      ZLXMJC,--项目简称
      ZLXMBH,--诊疗项目编号
      ZYZBID,--专业组别
      PYSM00,--拼音首码
      WBSM00,--五笔首码
      BH0000,--辅助编号
      DW0000,--单位
      1,
      null YBZXLB,--医保中心类别
      '非收费' YBLBMC,
      0 SFJE00,--单价
      '100%' ZFBL00,--自付比例
      '0' YBBRLB,--医保病人类别
      'N' SFSFXM --是否收费项目
from  VW_YS_YXSQZL
where not exists (select ZLXMID from XT_ZLSFGX where ZLXMID = VW_YS_YXSQZL.ZLXMID) order by pysm00
/