从多个表中汇总SQL数据

Question

我有两个表AP和INV，它们都有[PROJECT]和[Value]列。

我想要一个查询来返回如下结果：

PROJECT | SUM_AP | SUM_INV

我编写了以下代码，但它返回的结果是错误的（求和不正确）。

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]

- Iulian

Answer 1

查询结果有误是因为您尝试汇总的值被分组，导致重复的值包含在总和计算中。

您可以通过几个子查询来解决此问题：

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]

- Prutswonder

---

我收到了“您尝试执行一个查询，该查询未将指定的表达式'Project'作为聚合函数的一部分包含在内”的错误信息。 - Iulian

我更新了第二个子查询，避免了另一个分组列。你可以再试一次吗？ - Prutswonder

Answer 2

如果在一个给定项目ID下，你在AP表中有N行，而在INV表中该ID下有M行，则在这两个表上基于项目ID进行联接将会对于该项目总共产生N*M行。这是因为在AP表中相同的一行将会针对每一个具有该项目ID的INV表中的行重复出现，反之亦然。因此，你的计数很可能不准确（因为由于联接而从给定的表中多次计算同一行）。

相反，你可以尝试在两个子查询的结果之间进行联接，其中一个子查询通过项目ID对第一个表进行分组并对其求和，另一个子查询通过项目ID对另一个表进行分组并对其求和 - 然后在只有每个项目ID的总和的1行时进行联接。

- Amber

Answer 3

这个查询怎么样？

select SUM(gpCutBody.actualQty) as cutQty   , SUM(gpSewBody.quantity) as sewQty

from jobOrder
inner join gpCutHead on gpCutHead.joNum = jobOrder.joNum
inner join gpSewHead on gpSewHead.joNum = jobOrder.joNum

inner join gpCutBody on gpCutBody.gpCutID = gpCutHead.gpCutID
inner join gpSewBody on gpSewBody.gpSewID = gpSewHead.gpSewID


where jobOrder.joNum = '36'

这是ERD的链接: http://dl.dropbox.com/u/18794525/AUG%207%20DUMP%20STAN.png

- Mikko Fullero

---

你是否要从你的数据库发布ERD？我认为最好回答OP提供的信息。 - Gonzalo.-

Answer 4

如果PROJECT是父表，您应该从project表中进行选择，并在两个子表上执行左外连接：

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

- Marcus Adams

Answer 5

你可以将这两个求和计算分开。我能想到的一种方法是将库存计算移动到子查询中，例如：

SELECT 
    AP.[PROJECT]
,   SUM(AP.Value) AS SUM_AP
,   SummedInv as SUM_INV
FROM AP
LEFT JOIN (
    SELECT PROJECT, SUM(Value) AS SUM_INV
    FROM INV
    GROUP BY PROJECT
) SummedInv ON SummedInv.Project = AP.Project
GROUP BY AP.PROJECT, SummedInv.SUM_INV

由于SummedInv子查询已经按project分组，因此在外部查询中也可以安全地按SummedInv.SUM_INV进行分组。

- Andomar

Answer 6

尝试：

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

- lugte098