sql汇总多个表中的数据 [英] sql sum data from multiple tables

问题描述

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

I have 2 tables AP and INV where both have the columns [PROJECT] and [Value].

我希望查询返回如下内容:

I want a query to return something like this :

项目 |SUM_AP |SUM_INV

PROJECT | SUM_AP | SUM_INV

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

I came up with the code below but it's returning the wrong results ( sum is wrong ).

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]

推荐答案

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

The results from your query are wrong because the values you are trying to summarize are being grouped, which causes duplicate values to be included in the SUM.

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

You could solve it with a couple of sub-selects:

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]

这篇关于sql汇总多个表中的数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！