sql汇总多个表中的数据

本文介绍了sql汇总多个表中的数据的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

我有 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]