PostgreSQL LEFT JOIN 统计时零记录组织不显示

按组织统计已使用的记录时，查询已经写了 LEFT JOIN，结果却只剩有匹配记录的组织。没有记录的组织不显示，只有未使用记录的组织也不显示。给 COUNT() 外面加 COALESCE(..., 0)，结果仍然少行。

如果查询把右表的筛选条件放在 WHERE，这些组织可能在聚合之前就被过滤掉了。这里讨论 PostgreSQL 中“保留所有组织，只统计符合条件的右表记录”的查询；需要排除无匹配组织的业务不应照此修改。

先看缺失的行在哪一步被过滤

下面的查询看似要列出全部组织，实际上只保留关联记录满足两个条件的行：

SELECT o.id, o.name, COUNT(e.id) AS used_count
FROM organisations AS o
LEFT JOIN exam_items AS e
  ON e.organisation_id = o.id
WHERE e.item_template_id = 42
  AND e.used IS TRUE
GROUP BY o.id, o.name
ORDER BY o.id;

42 是示例模板编号，应换成应用绑定的查询参数。假设 exam_items.id 是非空主键。

LEFT JOIN 为完全没有匹配项的组织补上一行，右表各列都是 NULL。随后 WHERE 检查这一行：模板编号比较不会得到真，e.used IS TRUE 也不成立，因此整行被丢弃。对于确实有关联项、但关联项都不符合筛选条件的组织，已有行同样会被过滤。

COALESCE 只能替换表达式中的空值，无法把已经消失的组织行重新生成。这里应先修复连接与过滤的位置，再检查计数。

把“哪些右表记录参与匹配”写进 ON

若要求保留全部组织，模板和使用状态应作为右表匹配条件：

SELECT o.id, o.name, COUNT(e.id) AS used_count
FROM organisations AS o
LEFT JOIN exam_items AS e
  ON e.organisation_id = o.id
 AND e.item_template_id = 42
 AND e.used IS TRUE
GROUP BY o.id, o.name
ORDER BY o.id;

这次只有符合条件的记录能与组织匹配。没有符合条件的记录时，外连接仍为组织补一行右表为空的结果，组织就能进入分组阶段。

COUNT(e.id) 只统计非空的右表主键，因此补出的那一行计为零。不要在此处换成 COUNT(*)：它会把补出的行也计入，使零记录组织显示为一。COUNT(e.id) 的零计数本身不需要 COALESCE。

分组同时使用组织 ID 和名称，避免两个同名组织被合并。如果还要筛选组织自身的属性，这类左表条件仍可放在 WHERE；应根据业务意图区分“排除哪些组织”和“计入哪些记录”，而不是把所有条件机械搬进 ON。

“允许右表 ID 为空”不能覆盖所有情况

一种容易漏测的改法是保留原连接，再写：

WHERE (e.item_template_id = 42 AND e.used IS TRUE)
   OR e.id IS NULL

它能保住完全没有关联记录的组织，却未必保住“有关联记录，但全部不合条件”的组织。后一种组织在连接时已经匹配到真实记录，连接不会额外补一行空记录；WHERE 丢掉这些真实记录后，组织仍然消失。

因此，验收不能只测试空表，还要包含“只有不合条件记录”的组织。

用三个组织核对结果

下面的只读查询构造三类输入，不需要创建或修改业务表：

WITH organisations(id, name) AS (
  VALUES (1, 'no_items'), (2, 'unused_only'), (3, 'used_item')
), exam_items(id, organisation_id, item_template_id, used) AS (
  VALUES (101, 2, 42, false), (102, 3, 42, true)
)
SELECT o.id, o.name, COUNT(e.id) AS used_count
FROM organisations AS o
LEFT JOIN exam_items AS e
  ON e.organisation_id = o.id
 AND e.item_template_id = 42
 AND e.used IS TRUE
GROUP BY o.id, o.name
ORDER BY o.id;

按查询语义，三个组织都应出现，计数依次为 0、0、1。接着可在测试数据中加入另一个模板的记录、同名组织和多条合条件记录，确认模板过滤、分组身份和计数都符合要求。

如果实际查询还连接其他一对多表，同一条 exam_items 记录可能被重复展开。这是另一层计数问题，不能靠移动 WHERE 条件自动解决，应分别核对连接前后的记录粒度。

这项修改只调整查询，不需要改表或补造零值记录。上线前保留原查询，对照一批已知组织核验新增返回行及计数；若应用只接受有匹配项的组织，应先确认接口约定，再决定是否替换查询。