访问SQL查询返回错误的计数值字段

debugcn 发表于 Dev

亚当·梅里菲尔德

我正在尝试SQL查询一个查询，该查询的名称是Customer的名称，再加上一列c是Count使用Customers.[Customer ID] = Referrals.[Referred From]和连接到引荐的行数Referrals.[Used Our Service] = True

第二列c2是Count通过Customers.[Customer ID] = Referrals.[Referred From]和连接到引荐链接的行数Referrals.[Valid Referral] = True

此查询工作正常，但在c2列中返回错误的数字。当应为1时返回2。

SELECT 
Customers.[Customer ID], Customers.[First Name], Customers.[Last Name], Count(r.[Valid Referral]) AS c, Count(rr.[Used Our Service]) AS c2
FROM (Customers 
INNER JOIN Referrals r ON Customers.[Customer ID] = r.[Referred From] AND (r.[Valid Referral])=True)
INNER JOIN Referrals rr ON Customers.[Customer ID] = rr.[Referred From] AND (rr.[Used Our Service])=True
WHERE (r.[Valid Referral])=True
GROUP BY Customers.[Customer ID], Customers.[First Name], Customers.[Last Name]

客户表：

在此处输入图片说明

推介表：

在此处输入图片说明

结果表：

在此处输入图片说明

不

我建议您不要与SUM一起使用两次相同的表Iif，如下所示：

SELECT 
Customers.[Customer ID], 
Customers.[First Name], 
Customers.[Last Name], 
SUM(Iif(r.[Valid Referral] = True, 1, 0)) AS c, 
Sum(Iif(r.[Used Our Service] = True, 1, 0)) AS c2
FROM Customers 
INNER JOIN Referrals r ON Customers.[Customer ID] = r.[Referred From] 
WHERE (r.[Valid Referral])=True
GROUP BY Customers.[Customer ID], Customers.[First Name], Customers.[Last Name]

本文收集自互联网，转载请注明来源。

如有侵权，请联系[email protected] 删除。