sql汇总多个表中的数据

Question

我有 2 个表 AP 和 INV，其中都有 [PROJECT] 和 [Value] 列。

我想要一个查询返回这样的东西:

项目 | SUM_AP | SUM_INV

我想出了下面的代码，但它返回了错误的结果( sum 是错误的)。

SELECT AP.[PROJECT],
SUM(AP.Value) AS SUM_AP, 
SUM(INV.Value) AS SUM_INV
FROM AP INNER JOIN INV ON (AP.[PROJECT] =INV.[PROJECT])
WHERE AP.[PROJECT] = 'XXXXX'
GROUP BY AP.[PROJECT]

Answer 1

您的查询结果是错误的，因为您尝试汇总的值正在分组，这会导致 SUM 中包含重复的值。 .

您可以通过几个子选择来解决它:

SELECT 
    AP1.[PROJECT],
    (SELECT SUM(AP2.Value) FROM AP AS AP2 WHERE AP2.PROJECT = AP1.PROJECT) AS SUM_AP,
    (SELECT SUM(INV2.Value) FROM INV AS INV2 WHERE INV2.PROJECT = AP1.PROJECT) AS SUM_INV
FROM AP AS AP1 
    INNER JOIN INV AS INV1 
        ON (AP1.[PROJECT] =INV1.[PROJECT])
WHERE AP1.[PROJECT] = 'XXXXX'
GROUP BY AP1.[PROJECT]