为什么我的 LEFT JOIN 查询不返回计数为零的组织？

PostgreSQL LEFT JOIN：解决零计数组织问题

在 PostgreSQL 中使用 LEFT JOIN 时的一个常见挑战涉及检索关联记录的计数。 如果特定组织的计数为零，则该组织可能会从结果中省略。 本文解决了这个问题。

以下查询尝试对每个组织的已使用项目进行计数：

<code class="language-sql">SELECT o.name as organisation_name,
         coalesce(COUNT(exam_items.id)) as total_used
  FROM organisations o
  LEFT JOIN exam_items e on o.id = e.organisation_id
  WHERE e.item_template_id = #{sanitize(item_template_id)}
  and e.used = true
  GROUP BY o.name
  ORDER BY o.name</code>

此查询的缺陷在于 WHERE 子句。 WHERE 子句中放置的条件过滤 连接之后，当在 LEFT JOIN 中找不到匹配记录时，有效地将 INNER JOIN 转换为 exam_items。

以下是改进版本：

解决方案 1：更正 JOIN 条件

WHERE 子句过滤掉计数为零的组织。 将 item_template_id 和 used 条件移至 ON 的 LEFT JOIN 子句中可以解决此问题：

<code class="language-sql">SELECT o.name AS organisation_name, COUNT(e.id) AS total_used
FROM organisations o
LEFT JOIN exam_items e ON e.organisation_id = o.id AND e.item_template_id = #{sanitize(item_template_id)} AND e.used
GROUP BY o.name
ORDER BY o.name;</code>

此修订后的查询可确保包含所有组织，即使是 exam_items 中没有匹配条目的组织。

解决方案2：预聚合以提高效率

对于较大的数据集，预聚合exam_items可以显着提高性能：

<code class="language-sql">SELECT o.id, o.name AS organisation_name, COALESCE(e.total_used, 0) AS total_used
FROM organisations o
LEFT JOIN (
   SELECT organisation_id AS id, COUNT(*) AS total_used
   FROM exam_items
   WHERE item_template_id = #{sanitize(item_template_id)} AND used = TRUE
   GROUP BY 1
) e ON o.id = e.id
ORDER BY o.name, o.id;</code>

此方法首先聚合每个组织的计数，然后执行 LEFT JOIN，从而实现更高效的查询。 请注意使用 COALESCE 来处理 total_used 为 NULL 的情况。

通过实施这些解决方案中的任何一个，您将准确检索所有组织的已用项目数，包括那些计数为零的组织。 选择最适合您的数据大小和性能要求的解决方案。

以上是为什么我的 LEFT JOIN 查询不返回计数为零的组织？的详细内容。更多信息请关注PHP中文网其他相关文章！