问题描述:
在工作中需要开发一张复杂报表,需要关联的表比较多,在查询是用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
对金额的求和结果会成倍增加。