多个左连接如何导致 SQL 中的计数结果不正确，以及如何修复此问题？

SQL 多重左连接导致计数结果错误的陷阱及解决方案

在使用 SQL 时，如果多个左连接没有正确处理，可能会产生意想不到的结果。考虑以下查询：

SELECT
    t1."id" AS "User ID",
    t1.account_balance AS "Account Balance",
    count(t2.user_id) AS "# of grocery visits",
    count(t3.user_id) AS "# of fishmarket visits"
FROM users t1
LEFT OUTER JOIN grocery t2 ON (t2.user_id=t1."id") 
LEFT OUTER JOIN fishmarket t3 ON (t3.user_id=t1."id") 
GROUP BY t1.account_balance,t1.id
ORDER BY t1.id

此查询试图计算 users 表中每个用户的两个相关表 grocery 和 fishmarket 的访问次数。但是，由于左连接的特性，它会产生不正确的结果。

左连接的误区

在 SQL 中，连接是从左到右执行的。在此查询中，首先执行 users 和 grocery 之间的左连接。这导致每个用户记录与其对应的杂货购买记录连接。接下来，执行第一个连接结果与 fishmarket 之间的第二个左连接，这意味着每个带有杂货购买记录的用户记录将进一步与其对应的鱼市场购买记录连接。

乘法而非加法

这种顺序处理的意外结果是，来自 grocery 和 fishmarket 的访问计数被相乘而不是相加。例如，如果一个用户有 3 次杂货访问和 4 次鱼市场访问，则查询将产生 12 次访问，而不是预期的 7 次。

解决方案：使用子查询进行聚合

为了纠正这个问题，我们需要确保在连接表之前执行访问次数的聚合（计数）。这可以通过使用子查询来实现：

SELECT u.id
, u.account_balance
, g.grocery_visits
, f.fishmarket_visits
FROM   users u
LEFT   JOIN (
   SELECT user_id, count(*) AS grocery_visits
   FROM   grocery
   GROUP  BY user_id
   ) g ON g.user_id = u.id
LEFT   JOIN (
   SELECT user_id, count(*) AS fishmarket_visits
   FROM   fishmarket
   GROUP  BY user_id
   ) f ON f.user_id = u.id
ORDER  BY u.id;

此修改后的查询在子查询中聚合访问次数，然后将聚合的结果与 users 表连接，确保访问次数不会被意外地相乘。

以上是多个左连接如何导致 SQL 中的计数结果不正确，以及如何修复此问题？的详细内容。更多信息请关注PHP中文网其他相关文章！