為什麼我的 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中文網其他相關文章！