SQL中COUNT DISTINCT多表关联去重方法详解

我们先拆一下背后的执行机制，再一步步把坑填平。

先说结论：LEFT JOIN 后 COUNT(*) 虚高，本质上是 JOIN 先“炸开”行，再分组——主表一行变成了多行。COUNT(DISTINCT o.id) 能快速修复，但治本的办法还是预聚合，避开所有膨胀隐患。

为什么 LEFT JOIN 后 COUNT(*) 会虚高

因为 JOIN 会先把行“展开”，然后才聚合。举个例子：一个车主有三辆车，LEFT JOIN 之后，这条车主记录就被复制成了三行。这时你用 COUNT(*) 统计，得到的是 3，而不是 1 个车主。哪怕你只打算统计“有多少个车主”，结果也是 3。

常见的翻车现象：

• 想查“每个用户的订单数”，结果全是 1 —— 因为 COUNT(*) 在 LEFT JOIN 下恒定为 1（如果右表有匹配行的话）
• 想查“总订单金额”，数值翻倍甚至更高 —— 明细行反复拉了主表的金额字段
• COUNT(o.id) 和 COUNT(*) 结果一样 —— 说明右表压根没数据，或者 JOIN 条件忘了写对

那为什么会出现这种情况？问题就出在这儿——JOIN 的底层逻辑，是先“炸开”行，再动手分组。不先把膨胀源堵住，后面的统计全是在错误的数据上干活。

COUNT(DISTINCT 主键) 是最直接的补救写法

在已经写了 JOIN 的查询里，不改结构的前提下，用 COUNT(DISTINCT o.id) 代替 COUNT(*)，能快速绕过膨胀问题。

使用场景：

• 主表 ID 明确非空、唯一，且你想统计“有多少个主表实体被关联到”
• 临时排查或报表 SQL 不能大改时，作为兜底方案
• MySQL、PostgreSQL、SQL Server 都支持，语法兼容性不用担心

但有个细节要警惕：COUNT(DISTINCT v.owner_id) 不等于 COUNT(DISTINCT o.id)。前者统计的是“被引用的车主 ID 数”，可能漏掉那些没有车的车主；后者才对应左表的实际行数。

多列组合去重必须用子查询包装

如果你想统计“不同车主 + 城市组合数”，直接写 COUNT(DISTINCT o.id, o.city) 会出问题——MySQL 和 SQL Server 会报语法错误，PostgreSQL 虽然支持但语义容易让人误解。

正确的做法是把去重逻辑提前：

SELECT COUNT(*) FROM (
  SELECT DISTINCT o.id, o.city
  FROM owners o
  LEFT JOIN vehicle v ON v.owner_id = o.id
) t;

这里必须注意：

• 子查询里的 DISTINCT，消除的是 JOIN 后膨胀出来的重复组合，不是原始主表的行
• 如果要保留没有关联车辆的车主，必须用 LEFT JOIN，换成 INNER JOIN 就漏掉了
• 大数据量时，DISTINCT 在子查询里执行，比在窗口函数里用 COUNT(DISTINCT ...) OVER() 更稳定——后者除了 Presto/Trino，多数引擎都不支持

真正治本：预聚合再 JOIN，别让 COUNT 扛膨胀

所有靠 DISTINCT 补救的写法，本质上都是在“擦屁股”。长期维护或性能敏感的场景，必须把聚合前移。

例如统计每个车主的车辆数和总排量：

SELECT 
  o.name,
  COALESCE(v_agg.cnt, 0) AS vehicle_count,
  COALESCE(v_agg.total_cc, 0) AS total_engine_cc
FROM owners o
LEFT JOIN (
  SELECT owner_id, COUNT(*) AS cnt, SUM(engine_cc) AS total_cc
  FROM vehicle
  GROUP BY owner_id
) v_agg ON o.id = v_agg.owner_id;

这样做的好处很明显：

• 不会因为主表与右表匹配后行数变化，结果稳定可预测
• 即使某车主没有车，COALESCE 也能返回 0，不用再写 UNION 或条件判断
• 聚合在子查询内完成，数据库可以用 owner_id 索引加速，比全表 DISTINCT 快得多

最后说一个最容易忽略的陷阱：预聚合子查询里的 GROUP BY 字段，必须和 JOIN 条件完全一致。写成 GROUP BY v.owner_id 没问题，但如果你误写为 GROUP BY v.id，整个逻辑就彻底崩了。

总结一下：COUNT(DISTINCT) 是快速修复器，但预聚合才是长期稳定的方案。遇到对应场景，知道怎么选就好。