create or replace view vw_zy_jsfyhz as
select
     a.BRID00,            --病人ID
     a.ZYID00,            --住院ID
     a.ZYH000,            --住院号
     a.XM0000,            --姓名
     a.JZDH00,            --结账单号
     a.XMBH00,            --编码    医疗收费编码或药品编码
     a.GJBZBM as GJBM00,            --国家编码
     a.XMMC00,            --项目名称
     a.XMGG00,            --药品才有规格
     a.XMDW00,            --单位
     a.ZFBL00*100 as ZFBL00,        --自付比例
     sum(a.XMSL00) as XMSL00,       --数量
     sum(a.HJJE00) as HJJE00,       --合计金额
     round(sum(a.HJJE00)/sum(a.XMSL00),3) as XMDJ00,  --单价
     sum(a.ZFJE00) as ZFJE00,       --自费金额
     sum(a.GFJE00) as GFJE00,       --公费金额
     sum(a.JZJE00) as JZJE00,       --记帐金额
     sum(a.JMJE00) as JMJE00        --减免金额
from ZY_FYMX00 a
  group by a.BRID00,a.ZYID00,a.ZYH000,a.XM0000,a.JZDH00,a.XMBH00,a.GJBZBM,a.XMMC00,a.XMGG00,a.XMDW00,a.ZFBL00*100
     having(sum(a.XMSL00)<>0)
;