有个需求,数据库a表(报销单)存放的是时间及一些报销的金额,b表(报销类型)存放的是一些报销的类型名称,有10个报销类型。现在需求是求某4个报销类型的月累计金额,这个怎么做啊。
下面是我写的sql,其余的leftjoin是一些其他的表的数据,重点是er_bxzb是报销单,bd_inoutbusiclass是报销的类型。大佬帮忙看看怎么改
select er_bxzb.djbh,
er_bxzb.djrq,
er_bxzb.yearmonth,
er_bxzb.a,
bd_inoutbusiclass.name,
er_bxzb.total from (
select
er_bxzb.djbh djbh,
er_bxzb.djrq djrq,
er_bxzb.checktype,
er_bxzb.deptid_v,
er_bxzb.szxmid,
er_bxzb.creator,
to_char(to_date(er_bxzb.djrq,'yyyy-mm-dd hh24:mi:ss'),'yyyy-mm') yearmonth,
er_bxzb.total total,
bd_inoutbusiclass.name,
sum(er_bxzb.total) a
from er_bxzb er_bxzb
LEFT JOIN bd_inoutbusiclass bd_inoutbusiclass on er_bxzb.szxmid=bd_inoutbusiclass.pk_inoutbusiclass
where bd_inoutbusiclass.name in ('盘运装卸费','招待所保洁费','各分厂劳务费','主线切割废钢') and to_char(to_date(er_bxzb.djrq,'yyyy-mm-dd hh24:mi:ss'),'yyyy-mm')<=to_char(to_date(er_bxzb.djrq,'yyyy-mm-dd hh24:mi:ss'),'yyyy-mm')
GROUP BY er_bxzb.djbh,
er_bxzb.djrq,
er_bxzb.creator,
er_bxzb.szxmid,
er_bxzb.deptid_v,
er_bxzb.checktype,
to_char(to_date(er_bxzb.djrq,'yyyy-mm-dd hh24:mi:ss'),'yyyy-mm') ,
bd_inoutbusiclass.name,
er_bxzb.total
) er_bxzb LEFT JOIN bd_notetype bd_notetype on bd_notetype.pk_notetype=er_bxzb.checktype
LEFT JOIN bd_inoutbusiclass bd_inoutbusiclass on er_bxzb.szxmid=bd_inoutbusiclass.pk_inoutbusiclass
LEFT JOIN org_dept_v org_dept_v on org_dept_v.pk_vid=er_bxzb.deptid_v
LEFT JOIN sm_user sm_user on sm_user.cuserid=er_bxzb.creator where bd_inoutbusiclass.name in ('盘运装卸费','招待所保洁费','各分厂劳务费','主线切割废钢') and org_dept_v.name like '企业管理处%' and to_char(to_date(er_bxzb.djrq,'yyyy-mm-dd hh24:mi:ss'),'yyyy-mm')<=to_char(to_date(er_bxzb.djrq,'yyyy-mm-dd hh24:mi:ss'),'yyyy-mm')