sql - 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