create or replace view vw_yg_jzdyjsd as
select a.nf0000,--年份
a.xm0000,--姓名
sf_yg_getxb_cs(trim(a.sfzhao),'1') as xingb0,--性别
sf_yg_getxb_cs(trim(a.sfzhao),'2') as csrq00,--出生日期
a.id0000,--个人编号
c.RYLB00,--职工状态(人员类别)
a.sfzhao,--身份证号码
sf_yg_getdwmc(trim(a.id0000)) as dwmc00, --单位名称
b.yylj00,--月养老金
b.nylj00*0.25 as nylj00,--年养老金 --本年度住院情况
d.zyzfy0,--住院费用(住院总费用)
c.zymkf0,--住院门槛费
c.zytczf,--住院统筹支付费用
e.zyybn0 ,--住院医保内个人支付费用
e.zyybw0,--住院医保外个人支付费用
c.exzlzf,--恶性肿瘤住院个人支付费用 --本年度特殊病门诊情况
e.mzzfy0,--特殊病门诊费用(门诊总费用)
e.tsmkf0,--特殊病门诊门槛费
c.sbmkf0,--肾病门诊门槛费
c.sbgrzf,--肾病个人支付(肾病门诊乙类个人支付) --本年度医疗救助情况
a.yfje00,--应发金额(本年度应救助费用)
a.KJJE00 as ffje00,--发放金额(已发放救助费用)
c.zybzjz,--住院起付标准救助
c.exzljz,--恶性肿瘤医疗费救助
c.tsmkjz,--特殊病肾病门诊门槛费救助
c.tsyljz,--特殊病肾病门诊医疗费救助
c.zyyljz,--住院医疗费救助
a.kjje00 --扣减金额from yg_dyffb0 a,--待遇发放表
(select nf0000,id0000,round(avg(ylj000),2) as yylj00,sum(ylj000) as nylj00
from yg_yljdyb group by nf0000,id0000) b, --养老金取的是正确的
(select nf0000,id0000,RYLB00,--职工状态(人员类别)
sum(decode(jzlx00,'001',yfje00,0)) as zybzjz,--住院起付标准救助
sum(decode(jzlx00,'002',yfje00,0)) as zyyljz,--住院医疗费救助
sum(decode(jzlx00,'004',yfje00,0)) as exzljz,--恶性肿瘤医疗费救助
sum(decode(jzlx00,'005',yfje00,0)) as tsmkjz,--特殊病肾病门诊门槛费救助
sum(decode(jzlx00,'006',yfje00,0)) as tsyljz,特殊病肾病门诊医疗费救助
sum(decode(jzlx00,'001',mkf000,0)) as zymkf0,--住院门槛费
sum(decode(jzlx00,'002',grzfje,0)) as zytczf,--住院统筹支付费用
sum(decode(jzlx00,'004',grzfje,0)) as exzlzf,--恶性肿瘤住院个人支付费用
sum(decode(jzlx00,'005',mkf000,0)) as sbmkf0,--肾病门诊门槛费
sum(decode(jzlx00,'006',mkf000,0)) as sbgrzf --肾病个人支付(肾病门诊乙类个人支付)
from yg_jzdyb0 where cxbz00='Z'
group by nf0000,id0000,rylb00) c,
(select nf0000,id0000,sum(ylzfy0) as zyzfy0 from yg_grxxb0_zy group by nf0000,id0000) d,
(select nf0000,id0000,sum(ylzfy0) as mzzfy0,
sum(nvl(ZCGRZH,0)+nvl(ZCGRXJ,0)) as zyybn0,
sum(FYB000) as zyybw0,sum(MKF000) as tsmkf0
from yg_grxxb0_mz group by nf0000,id0000) ewhere a.nf0000=b.nf0000 and a.id0000=b.id0000
and a.nf0000=c.nf0000 and a.id0000=c.id0000
and a.nf0000=d.nf0000 and a.id0000=d.id0000
and a.nf0000=e.nf0000 and a.id0000=e.id0000这几个表的索引
执行计划
select a.nf0000,--年份
a.xm0000,--姓名
sf_yg_getxb_cs(trim(a.sfzhao),'1') as xingb0,--性别
sf_yg_getxb_cs(trim(a.sfzhao),'2') as csrq00,--出生日期
a.id0000,--个人编号
c.RYLB00,--职工状态(人员类别)
a.sfzhao,--身份证号码
sf_yg_getdwmc(trim(a.id0000)) as dwmc00, --单位名称
b.yylj00,--月养老金
b.nylj00*0.25 as nylj00,--年养老金 --本年度住院情况
d.zyzfy0,--住院费用(住院总费用)
c.zymkf0,--住院门槛费
c.zytczf,--住院统筹支付费用
e.zyybn0 ,--住院医保内个人支付费用
e.zyybw0,--住院医保外个人支付费用
c.exzlzf,--恶性肿瘤住院个人支付费用 --本年度特殊病门诊情况
e.mzzfy0,--特殊病门诊费用(门诊总费用)
e.tsmkf0,--特殊病门诊门槛费
c.sbmkf0,--肾病门诊门槛费
c.sbgrzf,--肾病个人支付(肾病门诊乙类个人支付) --本年度医疗救助情况
a.yfje00,--应发金额(本年度应救助费用)
a.KJJE00 as ffje00,--发放金额(已发放救助费用)
c.zybzjz,--住院起付标准救助
c.exzljz,--恶性肿瘤医疗费救助
c.tsmkjz,--特殊病肾病门诊门槛费救助
c.tsyljz,--特殊病肾病门诊医疗费救助
c.zyyljz,--住院医疗费救助
a.kjje00 --扣减金额from yg_dyffb0 a,--待遇发放表
(select nf0000,id0000,round(avg(ylj000),2) as yylj00,sum(ylj000) as nylj00
from yg_yljdyb group by nf0000,id0000) b, --养老金取的是正确的
(select nf0000,id0000,RYLB00,--职工状态(人员类别)
sum(decode(jzlx00,'001',yfje00,0)) as zybzjz,--住院起付标准救助
sum(decode(jzlx00,'002',yfje00,0)) as zyyljz,--住院医疗费救助
sum(decode(jzlx00,'004',yfje00,0)) as exzljz,--恶性肿瘤医疗费救助
sum(decode(jzlx00,'005',yfje00,0)) as tsmkjz,--特殊病肾病门诊门槛费救助
sum(decode(jzlx00,'006',yfje00,0)) as tsyljz,特殊病肾病门诊医疗费救助
sum(decode(jzlx00,'001',mkf000,0)) as zymkf0,--住院门槛费
sum(decode(jzlx00,'002',grzfje,0)) as zytczf,--住院统筹支付费用
sum(decode(jzlx00,'004',grzfje,0)) as exzlzf,--恶性肿瘤住院个人支付费用
sum(decode(jzlx00,'005',mkf000,0)) as sbmkf0,--肾病门诊门槛费
sum(decode(jzlx00,'006',mkf000,0)) as sbgrzf --肾病个人支付(肾病门诊乙类个人支付)
from yg_jzdyb0 where cxbz00='Z'
group by nf0000,id0000,rylb00) c,
(select nf0000,id0000,sum(ylzfy0) as zyzfy0 from yg_grxxb0_zy group by nf0000,id0000) d,
(select nf0000,id0000,sum(ylzfy0) as mzzfy0,
sum(nvl(ZCGRZH,0)+nvl(ZCGRXJ,0)) as zyybn0,
sum(FYB000) as zyybw0,sum(MKF000) as tsmkf0
from yg_grxxb0_mz group by nf0000,id0000) ewhere a.nf0000=b.nf0000 and a.id0000=b.id0000
and a.nf0000=c.nf0000 and a.id0000=c.id0000
and a.nf0000=d.nf0000 and a.id0000=d.id0000
and a.nf0000=e.nf0000 and a.id0000=e.id0000这几个表的索引
执行计划
解决方案 »
免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货