SQL求和函数和计数函数在多个表上产生乘积

Question

在无数次尝试让自己发疯后，我求助于你，希望有人愿意帮助我…

我有4个表：SO(ServiceOrder(、PR(PreRecording(、IH(InvoiceHeader(和IL(InvoiceLine(，下面的列和行如下：

SO(服务订单(：

SO.No_
SO2101234
SO2101235
SO2101237
SO2101239

您可以简单地嵌套查询，以便从平坦的内部SELECT执行外部SELECT。如果您的RDBMS版本不支持WITHCTE，这也会起作用。

为了保留具有NULL值的行，我使用了LEFT JOINS和COALESCE函数来获得0而不是NULL作为结果，正如您所希望的结果一样。

SELECT PRGRP.No_, 
COALESCE(PRGRP.SumAmount, 0) AS PRAmount,
COALESCE(SUM(IL.LineAmount), 0) AS ILAmount
FROM (   SELECT SO.No_, SUM(PR.Amount) AS SumAmount
FROM ServiceOrder SO 
LEFT JOIN PreRecording PR 
ON PR.No_ = SO.No_
GROUP BY SO.No_
) PRGRP
LEFT JOIN InvoiceHeader IH 
ON IH.SO = PRGRP.No_
LEFT JOIN InvoiceLine IL 
ON IL.DocNo_ = IH.No_
GROUP BY PRGRP.No_

结果：

No_	PRMount	ILMount
SO2101234	68	188
SO2101235	0	133
SO2101237	0	0
SO2101239	190	195

一个简单的策略是在进行其他查找之前将结果压平。我不完全理解你回到这里需要什么，但这是总的想法。

with SOPR as (
/* first-level aggregated results */
select SO.No_, sum(PR.Amount) as PRAmount
from SO inner join PR on PR.No_ = SO.No_
group by SO.No_
)
select
SOPR.No_, min(SOPR.PRAmount) as PRAmount,
sum(IL.Amount) as ILAmount
/* now look up next level of details */
from SOPR
inner join IH on IH.SO = SOPR.No_
inner join IL on IL.DocNo_ = IH.No_
group by SOPR.No_