select md.billmaindataid mid, nvl(r.objectname,' ') 分部名称,nvl(m.objectname,' ') 门店名称 ,nvl(xn.objectname,' ') 项目名称,
decode(c.name,'新开门店预算申请表单','新开','重装门店预算申请表单','重装','办公区装修预算申请表单','办公区','超级店'),md.item50,
nvl(md.item51,' '),nvl(md.item116,0),
md.occurtime, c.no ,nvl(g.objectname,' ') 工程项目,nvl(x.objectname,' ') 项目细项,
nvl(bd.offoriginalcurrency,0),nvl(bd.originalcurrency,0),
s.objectname,z.objectname 是否在入名录,decode(c.status,1,'审批中',2,'审批结束',3,'已终止')
from cc_form c
innerjoin t_Cc_Billmaindata md on c.no=md.billnumber
leftjoin t_cc_billdetaildata bd on md.billmaindataid=bd.billmaindataid
leftjoin t_cc_object r on r.objectid=md.requisitionusercompany
leftjoin t_cc_object m on m.objectid=md.item52
leftjoin t_Cc_Object g on g.objectid=bd.dimaccount
leftjoin t_cc_object x on x.objectid=bd.dim06
leftjoin t_cc_object xn on xn.objectid=md.item12
leftjoin t_cc_object z on z.objectid=bd.item14
leftjoin t_cc_object s on s.objectid=bd.item13
where c.name like'%预算申请表单%'and c.status!=0and c.status!=-1and c.name like'%%'
select md2.billmaindataid bmid, sum(bd2.originalcurrency) money
from cc_form c2
innerjoin t_Cc_Billmaindata md2 on c2.no=md2.billnumber
leftjoin t_cc_billdetaildata bd2 on md2.billmaindataid=bd2.billmaindataid
where c2.name like'%预算申请表单%'groupby md2.billmaindataid
效果如下:
最后要将这两张表进行合并就可以了,代码如下:
select * from
(
select md.billmaindataid mid, nvl(r.objectname,' ') 分部名称,nvl(m.objectname,' ') 门店名称 ,nvl(xn.objectname,' ') 项目名称,
decode(c.name,'新开门店预算申请表单','新开','重装门店预算申请表单','重装','办公区装修预算申请表单','办公区','超级店'),md.item50,
nvl(md.item51,' '),nvl(md.item116,0),
md.occurtime, c.no ,nvl(g.objectname,' ') 工程项目,nvl(x.objectname,' ') 项目细项,
nvl(bd.offoriginalcurrency,0),nvl(bd.originalcurrency,0),
s.objectname,z.objectname 是否在入名录,decode(c.status,1,'审批中',2,'审批结束',3,'已终止')
from cc_form c
innerjoin t_Cc_Billmaindata md on c.no=md.billnumber
leftjoin t_cc_billdetaildata bd on md.billmaindataid=bd.billmaindataid
leftjoin t_cc_object r on r.objectid=md.requisitionusercompany
leftjoin t_cc_object m on m.objectid=md.item52
leftjoin t_Cc_Object g on g.objectid=bd.dimaccount
leftjoin t_cc_object x on x.objectid=bd.dim06
leftjoin t_cc_object xn on xn.objectid=md.item12
leftjoin t_cc_object z on z.objectid=bd.item14
leftjoin t_cc_object s on s.objectid=bd.item13
where c.name like'%预算申请表单%'and c.status!=0and c.status!=-1and c.name like'%%'
) innerjoin
(
select md2.billmaindataid bmid, sum(bd2.originalcurrency) money
from cc_form c2
innerjoin t_Cc_Billmaindata md2 on c2.no=md2.billnumber
leftjoin t_cc_billdetaildata bd2 on md2.billmaindataid=bd2.billmaindataid
where c2.name like'%预算申请表单%'groupby md2.billmaindataid
) on mid=bmid