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

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

图片说明

10个回答

这个涉及到连表操作了

 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
qq_36099001
qq_36099001 好。
大约 2 年之前 回复
darklinboxs
darklinboxs 老铁,不行,没有数据,你这个
大约 2 年之前 回复
 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


darklinboxs
darklinboxs 老铁,不行,没有数据,你这个
大约 2 年之前 回复

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;

darklinboxs
darklinboxs 老铁,不行呀,没数据出来。
大约 2 年之前 回复

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

darklinboxs
darklinboxs 有数据了,可是不行,金额为0,是以cbs为分组,不要合并cbs金额。
大约 2 年之前 回复

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

darklinboxs
darklinboxs 不行,老铁。
大约 2 年之前 回复

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
图片说明

yayi52013
王不易 回复darklinboxs: 结果都出来了 看截图
接近 2 年之前 回复
darklinboxs
darklinboxs 老铁,不行,继续加油。
大约 2 年之前 回复

是哪里不行呢?项目名称只有一个,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

图片说明

Csdn user default icon
上传中...
上传图片
插入图片
抄袭、复制答案,以达到刷声望分或其他目的的行为,在CSDN问答是严格禁止的,一经发现立刻封号。是时候展现真正的技术了!
立即提问