数据库查询合并，数据库是sql server 2014,求大神指点

数据库查询合并，要实现的效果，见截图（实现的报表效果），颜色相同的即为关联的.
求大神指点，项目名称不能重复哟，cbs科目也不能重复。

这个涉及到连表操作了

 select 
a.project_name as '项目名称',
d.cbs_name as 'cbs科目',
c.act_amt as '预算',
e.remain_fc + g.je as '付款',
c.act_amt - e.remain_fc - g.je as '余额'

from 
project_table a
left join ays_cost_m b on a.pc=b.pc
left join sya_cost_d c on b.code=c.p_code
left join bd_cbs d on c.cbs=d.cbs
left join cnt_d e on d.cbs=e.cbs
left join zi_other f on e.pc=f.pc
left join zi_plan_pay g on f.code=g.code

 select 
aa.pname,aa.cname,aa.cnsid,
cc.act_amt , ee.remain_fc+ff.je fk ， cc.act_amt-ee.remain_fc-ff.je from
(
select 
a.project_name pname ,d.cbs_name cname,c.cns cntid
from 
project_table  a,
ays_cost_m  b,
asy_cost_d c,
bd_cbs  d
where a.pc=b.pc and b.code=c.p_code and c.cbs=d.cbs
group by a.project_name,d.cbs_name,c.cnt 
) aa,
asy_cost_d cc,
cnt_d  ee,
zj_plan_pay ff
where aa.cnsid=cc.cbs and 
cc.cbs=ee.cbs and ee.cbs=ff.cbs

group by 下

select project_table.project_name,
bd_cbs.cbs_name,
ays_cost_d.act_amt,
(cnt_d.remain_fc+zj_plan_pay.je),
ays_cost_m.act_amt-(cnt_d.remain_fc+zj_plan_pay.je)
from project_table,ays_cost_m,ays_cost_d,bd_cbs,cnt_d,zi_other,zi_plan_pay
where project_table.pc=ays_cost_m
and ays_cost_m.code=ays_cost_d.p_code
and ays_cost_d.cbs=bd_cbs.cbs
and project_table.pc=cnt_d.pc
and project_table.pc=zi_other.pc
and zi_other.code=zj_plan_pay.code;

select project_table.pc as '项目名称',bd_cbs.cbs_name as 'cbs科目',sum(act_amt) as '预算',(sum(cnt_d.remain_fc)+sum(zj_plan_pay.je)) as '付款',(sum(act_amt)-sum(cnt_d.remain_fc)-sum(zj_plan_pay.je)) as '余额'
from project_table
left join ays_cost_m on project_table.pc = ays_cost_m.pc
left join ays_cost_d on ays_cost_m.code = ays_cost_d.p_code
left join bd_cbs on ays_cost_d.cbs = bd_cbs.cbs
left join cnt_d on project_table.pc = cnt_d.pc
left join zi_other on project_table.pc = zi_other.pc
left join zj_plan_pay on zi_other.code = zj_plan_pay.code
group by project_table.pc,bd_cbs.cbs_name

select
a.project_name as '项目名称',
d.cbs_name as 'cbs科目',
c.act_amt as '预算',
e.remain_fc + g.je as '付款',
c.act_amt - e.remain_fc - g.je as '余额'

from
project_table a
left join ays_cost_m b on a.pc=b.pc
left join sya_cost_d c on b.code=c.p_code
left join bd_cbs d on c.cbs=d.cbs
left join cnt_d e on d.cbs=e.cbs
left join zi_other f on e.pc=f.pc
left join zi_plan_pay g on f.code=g.code
group by project_table.pc,bd_cbs.cbs_name

select
max(project_table.project_name)as '项目名称',
max(bd_cbs.cbs_name)as 'cbs科目',
sum(ays_cost_d.act_amt) as '预算',
(sum(cnt_d.remain_fc)+sum(zj_plan_pay.je)) as '付款',
(sum(act_amt)-sum(cnt_d.remain_fc)-sum(zj_plan_pay.je)) as '余额'
from project_table
left join ays_cost_m on project_table.pc = ays_cost_m.pc
left join ays_cost_d on ays_cost_m.code = ays_cost_d.p_code
left join bd_cbs on ays_cost_d.cbs = bd_cbs.cbs
left join cnt_d on bd_cbs.cbs=cnt_d.cbs

left join zi_other on project_table.pc = zi_other.pc
left join zj_plan_pay on zi_other.code = zj_plan_pay.code
group by project_table.pc,bd_cbs.cbs

是哪里不行呢?项目名称只有一个,cbs科目有两个，你说的项目名称不能重复，cbs科目不能重复是什么意思呢？

select
a.project_name as '项目名称',
d.cbs_name as 'cbs科目',
c.act_amt as '预算',
e.remain_fc + g.je as '付款',
c.act_amt - e.remain_fc - g.je as '余额'

from
project_table a
join ays_cost_m b on a.pc=b.pc
join ays_cost_d c on b.code=c.p_code
join bd_cbs d on c.cbs=d.cbs
join cnt_d e on d.cbs=e.cbs
join zi_other f on e.pc=f.pc
join zj_plan_pay g on f.code=g.code

考虑到科目的预算应该只有一个，合同付款金额和其他付款金额有多个。所以同付款金额和其他付款金额需要分组合计。

SELECT a.project_name AS '项目名称' ,
d.cbs_name AS 'cbs科目' ,
c.act_amt AS '预算' ,
e.remain_fc + f.je AS '付款' ,
c.act_amt - e.remain_fc - f.je AS '余额'
FROM project_table a
LEFT JOIN ays_cost_m b ON a.pc = b.pc
LEFT JOIN sya_cost_d c ON b.code = c.p_code
LEFT JOIN bd_cbs d ON c.cbs = d.cbs
LEFT OUTER JOIN ( SELECT pc ,
cbs ,
SUM(remain_fc) AS remain_fc
FROM cnt_d
GROUP BY pc ,
cbs
) e ON d.cbe = e.cbs
LEFT OUTER JOIN ( SELECT aaa.pc ,
aaa.cbs ,
SUM(je) AS je
FROM ( SELECT aa.pc ,
bb.cbs ,
bb.je
FROM zi_other aa
LEFT OUTER JOIN zi_plan_pay bb ON aa.code = bb.code
) aaa
GROUP BY aaa.pc ,
aaa.cbs
) f ON d.cbe = f.cbs

犯了一个错误。

SELECT c.project_name AS '项目名称' ,
d.cbs_name AS 'cbs科目' ,
a.act_amt AS '预算' ,
e.remain_fc + f.je AS '付款' ,
a.act_amt - e.remain_fc - f.je AS '余额'
FROM sya_cost_d a
LEFT JOIN ays_cost_m b ON c.p_code = b.code
LEFT JOIN project_table c ON b.pc = c.pc
LEFT JOIN bd_cbs d ON a.cbs = d.cbs
LEFT OUTER JOIN ( SELECT pc ,
cbs ,
SUM(remain_fc) AS remain_fc
FROM cnt_d
GROUP BY pc ,
cbs
) e ON a.cbs = e.cbs
LEFT OUTER JOIN ( SELECT aaa.pc ,
aaa.cbs ,
SUM(je) AS je
FROM ( SELECT aa.pc ,
bb.cbs ,
bb.je
FROM zi_other aa
LEFT OUTER JOIN zi_plan_pay bb ON aa.code = bb.code
) aaa
GROUP BY aaa.pc ,
aaa.cbs
) f ON a.cbs = f.cbs