首页 新闻 搜索 专区 学院

oracle使用sum()和left join查询的时候,查询结果会出现成倍增加

0
悬赏园豆:200 [已关闭问题] 关闭于 2018-01-17 15:06

问题描述:

在工作中需要开发一张复杂报表,需要关联的表比较多,在查询是用left join进行关联,查询出的金额数量会进行成倍增加,sql如下:

select
--distinct(pm_a.pk_mcontr_alter),
--distinct(ar.pk_gatheritem),
--distinct(pm.pk_project),
 'C0' cbs,'C0' father_cbs,
'项目收入' cbs_name,'项目收入' father_cbs_name,
--distinct pm.pk_mar_contr,
to_number(sum(nvl(pm.mny_curr,0))) htje,
count((pm.mny_curr)),
--count(distinct pm.pk_mar_contr),
to_number(sum(nvl(pm_a.alter_mny,0))) qrbg,    --所找单据值为0
to_number(0) ycbg,                             --预测变更,不取值
--to_number(0) yjzsr,
to_number(sum(nvl(pm_a.alter_mny,0))+ sum(nvl(pm.mny_curr,0))+sum(nvl(ar.settlemoney,0))) yjzsr,           --预计总收入   原合同金额,确认变更,预测变更
to_number(sum(nvl(ar.settlemoney,0))) sjsr,     --实际收入
count(ar.settlemoney),
to_number(0) yqhte, to_number(0) yqrbg,to_number(0) yjhtbg, to_number(0) yjzzc,to_number(0) yjlr,'' bz,
to_number(0) ys, to_number(0) sjzc ,pm.pk_project,bdp.project_code,bdp.project_name,bd_psndoc.name
from pm_mcontr pm
left join pm_mcontr_alter pm_a on pm.pk_mar_contr =pm_a.pk_mcontr
left join ar_gatheritem ar on ar.project = pm.pk_project
left join  bd_project bdp on ar.project = bdp.pk_project
left join  bd_psndoc bd_psndoc on bd_psndoc.pk_psndoc = bdp.pk_dutier
where  pm.bill_status = 9 and  nvl(pm.dr,0)=0 and nvl(ar.dr,0)=0 and nvl(bdp.dr,0)=0
--and pm.pk_project  = '1002A1100000000RC4LZ'
and bdp.project_code = 'PM201708080004'
and nvl(pm_a.dr,0) =0 and nvl(bd_psndoc.dr,0)= 0   
group by  pm.pk_project,bdp.project_code,bdp.project_name,bd_psndoc.name,
pm_a.pk_mcontr_alter,
ar.pk_gatheritem,
ar.settlemoney
对金额的求和结果会成倍增加。

小詹小詹的主页 小詹小詹 | 初学一级 | 园豆:16
提问于:2018-01-12 10:36
< >
分享
所有回答(2)
0

建议,把先做left join,然后把left join的结果集作为子查询,在求和。这样看表太多,或者创建一个视图把这些数据先整理好,在做求和等运算。

ckx0709 | 园豆:392 (菜鸟二级) | 2018-01-12 16:51

select * from(
select
---在这里补齐条件查询中需要的字段
--distinct(pm_a.pk_mcontr_alter),
--distinct(ar.pk_gatheritem),
--distinct(pm.pk_project),
'C0' cbs,'C0' father_cbs,
'项目收入' cbs_name,'项目收入' father_cbs_name,
--distinct pm.pk_mar_contr,
to_number(sum(nvl(pm.mny_curr,0))) htje,
count((pm.mny_curr)),
--count(distinct pm.pk_mar_contr),
to_number(sum(nvl(pm_a.alter_mny,0))) qrbg, --所找单据值为0
to_number(0) ycbg, --预测变更,不取值
--to_number(0) yjzsr,
to_number(sum(nvl(pm_a.alter_mny,0))+ sum(nvl(pm.mny_curr,0))+sum(nvl(ar.settlemoney,0))) yjzsr, --预计总收入 原合同金额,确认变更,预测变更
to_number(sum(nvl(ar.settlemoney,0))) sjsr, --实际收入
count(ar.settlemoney),
to_number(0) yqhte, to_number(0) yqrbg,to_number(0) yjhtbg, to_number(0) yjzzc,to_number(0) yjlr,'' bz,
to_number(0) ys, to_number(0) sjzc ,pm.pk_project,bdp.project_code,bdp.project_name,bd_psndoc.name
from pm_mcontr pm
left join pm_mcontr_alter pm_a on pm.pk_mar_contr =pm_a.pk_mcontr
left join ar_gatheritem ar on ar.project = pm.pk_project
left join bd_project bdp on ar.project = bdp.pk_project
left join bd_psndoc bd_psndoc on bd_psndoc.pk_psndoc = bdp.pk_dutier )lstable lst
--下边的全部改成lst.字段
where pm.bill_status = 9 and nvl(pm.dr,0)=0 and nvl(ar.dr,0)=0 and nvl(bdp.dr,0)=0
--and pm.pk_project = '1002A1100000000RC4LZ'
and bdp.project_code = 'PM201708080004'
and nvl(pm_a.dr,0) =0 and nvl(bd_psndoc.dr,0)= 0
group by pm.pk_project,bdp.project_code,bdp.project_name,bd_psndoc.name,
pm_a.pk_mcontr_alter,
ar.pk_gatheritem,
ar.settlemoney

支持(0) 反对(0) ckx0709 | 园豆:392 (菜鸟二级) | 2018-01-12 16:58

怎么吧left join的结果集作为子查询啊,我不太明白,这个应该是关联字段是一对多的关系造成的,我先选了两张表做查询,第一张表数据是正常的,但是第二张表数据就是正常数据的两倍。sql是这样的:

select
to_number(sum(pm_mcontr.mny_curr)) htje,sum(ar.sjccc) sjsr
from pm_mcontr pm_mcontr
left join (select sum(distinct settlemoney) sjccc,project from ar_gatheritem  where project = '1002A1100000000R' and dr = 0 group by project) ar
on ar.project = pm_mcontr.pk_project
where pm_mcontr.pk_project = '1002A1100000000R' and pm_mcontr.dr = 0

支持(0) 反对(0) 小詹小詹 | 园豆:16 (初学一级) | 2018-01-12 17:03

@小詹小詹: 

select
to_number(sum(pm_mcontr.mny_curr)) htje,sum(ar.sjccc) sjsr
from (select *from pm_mcontr where pk_project = '1002A1100000000R' ) pm_mcontr
left join (select sum(distinct settlemoney) sjccc,project from ar_gatheritem
where project = '1002A1100000000R' and dr = 0 group by project) ar
on ar.project = pm_mcontr.pk_project

直接把where后面的去掉。

支持(0) 反对(0) ckx0709 | 园豆:392 (菜鸟二级) | 2018-01-12 17:11
0

解决方案:把需要sum的数据先聚合,放到视图中,视图中的值为项目主键,以及所求的和,然后在最终的视图中来查询放sum值的视图,避免了多对多的情况出现,就是如果所求的sum值过多的时候,需要创建大量的视图,比较麻烦

小詹小詹 | 园豆:16 (初学一级) | 2018-01-17 15:04
清除回答草稿
   您需要登录以后才能回答,未注册用户请先注册