查询中导致不正确结果的两个计数
查询中导致不正确结果的两个计数
提问于 2015-06-05 13:32:05
使用
SELECT table1.FullName,
SUM(table2.Asp_PointsAwarded) AS 'Table2 Total Points',
table3.Name,
table4.Asp_PointsAwarded
FROM table1
LEFT JOIN table3 ON table1.ParentCustomerId = table3.AccountId
INNER JOIN table2 ON table1.ContactId = table2.Asp_SalemadebyId
INNER JOIN table4 ON table1.ContactId = table4.Asp_PointsAwardedToId
WHERE (table3.asp_mimanagerid = '10CACCBD-70A7-DD11-9C65-001EC9B77038')
GROUP BY table1.FullName, table3.Name, table4.asp_pointsawarded
ORDER BY table1.FullName我得到以下结果是正确的。89571已被正确地归纳为：
John Doe - 89571 - Contoso - 500
John Doe - 89571 - Contoso - 750
John Doe - 89571 - Contoso - 2000
John Doe - 89571 - Contoso - 26302
John Doe - 89571 - Contoso - 61606但是，当我尝试使用以下代码与table4.Asp_PointsAwarded相加时
SELECT table1.FullName,
SUM(table2.Asp_PointsAwarded) AS 'Table2 Total Points',
table3.Name,
SUM(table4.Asp_PointsAwarded) AS 'Table4 Total Points'
FROM table1
LEFT JOIN table3 ON table1.ParentCustomerId = table3.AccountId
INNER JOIN table2 ON table1.ContactId = table2.Asp_SalemadebyId
INNER JOIN table4 ON table1.ContactId = table4.Asp_PointsAwardedToId
WHERE (table3.asp_mimanagerid = '10CACCBD-70A7-DD11-9C65-001EC9B77038')
GROUP BY table1.FullName, table3.Name
ORDER BY table1.FullName我得到以下信息
John Doe - 447855 - Contoso - 41568048知道为什么会这样吗？看起来89571又加了5次，我不想发生。理想情况下，我希望看到以下输出：
John Doe - 89571 - Contoso - 91158回答 1
Stack Overflow用户
回答已采纳
发布于 2015-06-05 13:37:25
join为左手表中的每一行重复右手表中的所有匹配行。如果执行两个联接，它将重复第三个表中的所有行、第二个表中的所有行、第一个表中的所有行。那就意味着计数出了问题。
您可以通过在加入前进行总结来解决这个问题：
SELECT table1.FullName
, sub1.sum_points
, table3.Name
, sub2.sum_points
FROM table1
JOIN table3
ON table1.ParentCustomerId = table3.AccountId
JOIN (
SELECT Asp_SalemadebyId
, SUM(Asp_PointsAwarded) sum_points
FROM table2
GROUP BY
Asp_SalemadebyId
) sub1
ON table1.ContactId = sub1.Asp_SalemadebyId
JOIN (
SELECT Asp_PointsAwardedToId
, SUM(Asp_PointsAwarded) sum_points
FROM table4
GROUP BY
Asp_PointsAwardedToId
) sub2
ON table1.ContactId = sub2.Asp_PointsAwardedToId
WHERE table3.asp_mimanagerid = '10CACCBD-70A7-DD11-9C65-001EC9B77038')页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/30667856
复制相关文章
相似问题
腾讯云开发者
