如何用SQL COUNT DISTINCT解决多表关联下的虚高统计？

LEFT JOIN 后 COUNT(*) 虚高是因为 JOIN 先“炸开”行再分组，导致主表一行变多行；用 COUNT(DISTINCT o.id) 可修复，但治本之法是预聚合再 JOIN。

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

因为 JOIN 先“炸开”行，再分组。比如一个车主有 3 辆车，LEFT JOIN 后这条车主记录就变成 3 行；COUNT(*) 统计的是这 3 行，不是 1 个车主。哪怕你只 SELECT o.name, COUNT(*)，结果也是 3，而非业务想要的“该车主名下车辆数”。

常见错误现象：

• 查“每个用户的订单数”，结果全是 1（COUNT(*) 在 LEFT JOIN 下恒为 1）
• 查“总订单金额”，数值翻倍甚至更高（明细行重复拉取主表金额字段）
• COUNT(o.id) 和 COUNT(*) 结果一致——说明右表全为空或没生效，不是真去重

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_ccFROM owners oLEFT 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，整个逻辑就崩了。