为什么多个 SQL LEFT JOIN 会产生不正确的访问计数？

了解多个 SQL LEFT JOIN 的不准确访问计数

本文研究了一个常见的 SQL 查询问题：使用多个 LEFT JOIN 操作时获取不正确的访问计数。 目标是检索用户数据，包括帐户余额、杂货店访问量和鱼市场访问量。 简单的查询通常会产生夸大的结果（例如“1”、“12”、“12”）。

问题源于LEFT JOIN从左到右的执行。 如果连接的表中有多个匹配条目，则每个后续连接都会增加行数。 例如，将三个杂货表连接到一个用户会产生三行。 随后与四个鱼市场表的连接将其扩展到十二行，导致访问计数不准确。

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

解决方案涉及在将每个表加入到 users 表之前预先聚合每个表的访问计数。这可以防止行乘法问题。 更正后的查询使用子查询来实现此目的：

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;

使用 COALESCE 处理缺失值

为了优雅地处理用户没有访问特定位置的情况，COALESCE 函数可以将 NULL 值替换为 0（或任何其他所需值）：

SELECT u.id, u.account_balance, COALESCE(g.grocery_visits, 0) AS grocery_visits, COALESCE(f.fishmarket_visits, 0) AS 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;

这个改进的查询准确地反映了每个用户访问杂货店和鱼市场的次数。 通过预先聚合数据，我们避免了级联LEFT JOIN的陷阱并确保准确的结果。

以上是为什么多个 SQL LEFT JOIN 会产生不正确的访问计数？的详细内容。更多信息请关注PHP中文网其他相关文章！