标量子查询导致count翻倍，因每行触发全表扫描且重复行被计入；应改用窗口函数over(partition by)或内层去重聚合，避免外层distinct失效，并注意null值对in/not in及聚合函数的影响。

聚合重复计算不是子查询写错了，是执行逻辑被放大了——JOIN先拉出多行，COUNT再算一遍，结果就翻倍。必须把去重或聚合动作压到最内层，而不是在视图或外层加DISTINCT。

为什么标量子查询会让COUNT翻倍

像SELECT u.id, (SELECT COUNT(*) FROM orders WHERE user_id = u.id)这种写法，每查一个用户，就全表扫一次orders；10万用户 = 10万次扫描。更糟的是，如果orders里有脏数据（比如同一user_id和order_id重复出现），COUNT还会把重复行也计入。

• EXPLAIN里能看到多次Subquery执行，且type常为ALL或index
• 即使user_id有索引，优化器也很难复用前一次扫描结果
• 多个标量子查询并存时（比如同时查订单数、总金额、最新时间），性能呈线性恶化

用窗口函数替代标量子查询

把“每行触发一次子查询”改成“整表扫一遍，一次分组算完”，核心是用OVER(PARTITION BY ...)代替关联条件。

• 改写示例：SELECT DISTINCT u.id, u.name, COUNT(o.id) OVER(PARTITION BY u.id) AS order_count FROM users u LEFT JOIN orders o ON o.user_id = u.id
• DISTINCT必须加，否则LEFT JOIN会因一对多产生重复用户行
• MySQL 8.0+、PostgreSQL、SQL Server 2005+ 都支持，但Oracle更早支持
• 不能用于WHERE过滤（比如只查order_count > 5的用户），因为窗口函数结果不能在WHERE中引用

子查询里去重必须作用于原始明细

别指望视图或外层DISTINCT救场——聚合发生在去重之前，污染已发生。真正有效的去重，得在聚合前对参与计算的字段组合做清洗。

• 正确写法：SELECT user_id, COUNT(*) FROM (SELECT DISTINCT user_id, order_id FROM orders) t GROUP BY user_id
• DISTINCT字段组合（如user_id, order_id）必须有联合索引，否则排序开销巨大
• 要取每个用户的最新订单？子查询里用ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY created_at DESC)，再筛rn = 1
• 如果子查询里GROUP BY后仍返回重复user_id，先单独执行它查COUNT(*)，确认是否真收敛

聚合字段含NULL时的陷阱

COUNT(*)不受NULL影响，但COUNT(列名)、SUM、AVG会跳过NULL值；更隐蔽的是，IN子查询遇到NULL直接失效。

• 查“下过单的用户”别写WHERE id IN (SELECT user_id FROM orders)——只要orders.user_id有一条NULL，整个结果为空
• 换成EXISTS：WHERE EXISTS (SELECT 1 FROM orders WHERE user_id = u.id)，不依赖值比较，天然绕过NULL
• NOT IN比NOT EXISTS更危险：子查询结果里只要有一个NULL，整条语句就查不到任何数据
• 聚合前用COALESCE显式处理NULL，比如SUM(COALESCE(amount, 0))

最容易被忽略的点是：窗口函数快，但它的排序和内存缓冲依赖PARTITION BY字段的基数。如果按毫秒级时间戳分组，或者数据量远超数据库配置的work_mem，反而会触发磁盘临时文件，比标量子查询还慢。