SQL：用总和连接 3 个表，过滤非聚合列

Question

我有以下表格：

    Plant:
ID      Name
1       Plant1
2       Plant2
......

   Rejects:
PlantID Quantity  Date
1       20        01/02/2012
1       3         02/02/2012
2       30        03/02/2012
.....

  Parts
PlantID Quantity  Date
1       300       01/02/2012
2       500       01/02/2012
1       600       02/02/2012
.......

我正在尝试将这三个表连接起来，以便在两个日期之间获得每个工厂的零件和拒绝的总和：

Plant   Parts   Rejects
Plant1       900     23
Plant2       500     30
.....

我尝试过连接，它只是将总和相乘，并且我尝试过子查询，但会成功不允许我使用日期过滤器，因为它没有在 group by 子句中使用。

有人可以帮忙吗？

Answer 1

declare @startDate datetime, @endDate datetime
select @startDate = '1/1/2012', @endDate = '2/1/2012'

select
    p.Name as Plant,

    (select sum(Quantity) 
     from Parts 
     where PlantID = p.ID and date between @startDate and @endDate) as Parts,

    (select sum(Quantity) 
     from Rejects 
     where PlantID = p.ID and date between @startDate and @endDate) as Rejects
from
    Plant p
where
    p.endDate is null

Answer 2

这就能解决问题：

select p.Name as Plant,
       SUM(t.Quantity) as Parts,
       SUM(r.Quantity) as Rejects
from Plant p
inner join Rejects r on p.ID = r.PlantID
inner join Parts t on p.ID = PlantID
group by p.Name

您可以使用ID 在所有表之间进行INNER JOIN。之后，您只需要 Parts 和 Rejects 中的 Quantities 的 SUM 即可。为此，您需要使用GROUP BY。

Answer 3

以下内容应该有效（使用预言机）：

SELECT NVL(p.plantid, r.plantid), NVL(parts,0), NVL(rejects,0)
FROM 
    (SELECT plantid, sum(quantity) as parts
    FROM parts
    WHERE date BETWEEN a AND b) p
        FULL JOIN
    (SELECT plantid, sum(quantity) as rejects
    FROM rejects
    WHERE date BETWEEN a and b) r
       ON p.plantid = r.plantid

请注意，这不会使工厂没有废品或零件。