Sum返回正确答案..有时

Question

我有下面的SQL，它会返回正确的答案，直到第二个表匹配为止。应该只有一个"；1〃；在任何回报中，但我在不同的地方都得到了2分和3分。我在表格中有3个不同的日期，有时当它在PA Sim Cnt中遇到一些事情时，会影响2 Mtr Cnt中的回报。

SELECT [Membership tbl].[Call Sign], Sum(IIf([2 Meter Net tbl].[Cur Date]=#9/15/2020#,1,0)) AS [2 Mtr Cnt], Sum(IIf([PA Simplex tbl].[Cur Date]=#9/15/2020#,1,0)) AS [PA Sim Cnt]
FROM ([Membership tbl] LEFT JOIN [2 Meter Net tbl] ON [Membership tbl].[Call Sign] = [2 Meter Net tbl].[Call Sign]) LEFT JOIN [PA Simplex tbl] ON [Membership tbl].[Call Sign] = [PA Simplex tbl].[Call Sign]
GROUP BY [Membership tbl].[Call Sign]
ORDER BY [Membership tbl].[Call Sign];

我已经为此工作了好几天，并尝试了我能想到的每一种组合。

Answer 1

SELECT m.[Call Sign], Nz(mn.[2 Mtr Cnt],0) AS [2 Mtr Cnt], Nz(ps.[PA Sim Cnt],0) AS [PA Sim Cnt]
FROM ([Membership tbl] AS m 
LEFT JOIN (SELECT [Call Sign], 
Sum(IIf([2 Meter Net tbl].[Cur Date]=#9/15/2020#,1,0)) AS [2 Mtr Cnt] 
FROM [2 Meter Net tbl] 
GROUP BY [Call Sign])  AS mn ON m.[Call Sign] = mn.[Call Sign]) 
LEFT JOIN (SELECT [Call Sign], 
Sum(IIf([PA Simplex tbl].[Cur Date]=#9/15/2020#,1,0)) AS [PA Sim Cnt] 
FROM [PA Simplex tbl] 
GROUP BY [Call Sign])  AS ps ON m.[Call Sign] = ps.[Call Sign]
ORDER BY m.[Call Sign];

Answer 2

我怀疑在加入之前需要在子查询中进行预聚合，因此每个日期只计算一次：

SELECT m.[Call Sign], mn.[2 Mtr Cnt], ps.[PA Sim Cnt]
FROM (
[Membership tbl] m
LEFT JOIN (
SELECT [Call Sign], Sum(IIf([Cur Date]=#9/15/2020#,1,0)) AS [2 Mtr Cnt]
FROM [2 Meter Net tbl] 
GROUP BY [Call Sign]
) mn ON m.[Call Sign] = mn.[Call Sign]
) LEFT JOIN (
SELECT [Call Sign], Sum(IIf([Cur Date]=#9/15/2020#,1,0)) AS [PA Sim Cnt]
FROM [PA Simplex tbl] 
GROUP BY [Call Sign]
) ps ON m.[Call Sign] = ps.[Call Sign]
ORDER BY m.[Call Sign];