LEFT JOIN with SUM未返回正确值

Question

我有一个发票表，里面有各种成本明细。我有一个其他成本的链接表。关系是1张发票与许多杂项成本

当我执行SUM((并且杂项成本表(RIGHT(上存在多行时，发票表(LEFT(中的值将乘以找到的行数。

DB Fiddle：https://www.db-fiddle.com/f/9CKGRShXPoT3k4Wcm83fVD/5

LABOUR、PARTS、POLS和SUBLET:的原始查询结果不正确

SELECT  SUM(ad.labour_cost) AS LABOUR,
SUM(ad.part_cost) AS PARTS,
SUM(ad.pol_cost) AS POLS,
SUM(ad.sublet_cost) AS SUBLET,
SUM(am.misc_sales_amt)  AS MISC
FROM AdvisorSalesData ad 
LEFT JOIN AdvisorMiscSalesData am 
ON (ad.customer_id=am.customer_id AND ad.invoice_no=am.invoice_no)
WHERE ad.customer_id IN (3)

调整后的查询-删除了INNER JOIN，并在misc表上尝试了一次SELECT，但结果不正确(NULL(：

SELECT  SUM(ad.labour_cost) AS LABOUR,
SUM(ad.part_cost) AS PARTS,
SUM(ad.pol_cost) AS POLS,
SUM(ad.sublet_cost) AS SUBLET,
(SELECT SUM(misc_sales_amt) FROM AdvisorMiscSalesData WHERE customer_id IN (3) AND invoice_no=ad.invoice_no ) AS MISC
FROM AdvisorSalesData ad 
WHERE ad.customer_id IN (3)

如何调整以在一个查询中获得所有5列的正确结果？

Answer 1

加入前的聚合：

SELECT SUM(ad.labour) AS LABOUR,
SUM(ad.parts) AS PARTS,
SUM(ad.pols) AS POLS,
SUM(ad.sublet) AS SUBLET,
SUM(am.misc)  AS MISC
FROM (SELECT customer_id, invoice_no,
SUM(ad.labour_cost) AS LABOUR,
SUM(ad.part_cost) AS PARTS,
SUM(ad.pol_cost) AS POLS,
SUM(ad.sublet_cost) as sublet
FROM AdvisorSalesData ad 
GROUP BY customer_id, invoice_no
) ad LEFT JOIN
(SELECT customer_id, invoice_no,
SUM(am.misc_sales_amt) AS MISC
FROM AdvisorMiscSalesData am 
GROUP BY customer_id, invoice_no
) am
ON ad.customer_id = am.customer_id AND    
ad.invoice_no = am.invoice_no
WHERE ad.customer_id IN (3);

这是一个数据库<>不停摆弄

您可以在子查询中重复过滤器以获得更好的性能：

SELECT SUM(ad.labour) AS LABOUR,
SUM(ad.parts) AS PARTS,
SUM(ad.pols) AS POLS,
SUM(ad.sublet) AS SUBLET,
SUM(am.misc)  AS MISC
FROM (SELECT customer_id, invoice_no,
SUM(ad.labour_cost) AS LABOUR,
SUM(ad.part_cost) AS PARTS,
SUM(ad.pol_cost) AS POLS,
SUM(ad.sublet_cost) as sublet
FROM AdvisorSalesData ad 
WHERE ad.customer_id IN (3)
GROUP BY customer_id, invoice_no
) ad LEFT JOIN
(SELECT customer_id, invoice_no,
SUM(am.misc_sales_amt) AS MISC
FROM AdvisorMiscSalesData am 
WHERE ad.customer_id IN (3)
GROUP BY customer_id, invoice_no
) am
ON ad.customer_id = am.customer_id AND    
ad.invoice_no = am.invoice_no
WHERE ad.customer_id IN (3);