不能在LEFT JOIN的ON或WHERE中直接使用ROW_NUMBER(),必须用子查询或CTE先计算序号并别名,再在外层ON中加rn=1(不可放WHERE),否则丢失左表空匹配;PARTITION BY字段须与JOIN键一致,ORDER BY决定“第一条”且需索引支撑。

LEFT JOIN 里不能直接用 ROW_NUMBER() 窗口函数过滤?
不是不能,而是写法错了。很多人试图在 WHERE 或 ON 子句里直接写 ROW_NUMBER() OVER (...) = 1,结果报错:window function is not allowed in WHERE clause。窗口函数只能出现在 SELECT 或 ORDER BY 中,不能用于过滤逻辑——得先算出序号,再在外层筛选。
正确写法:子查询或 CTE 套一层
核心思路是把带 ROW_NUMBER() 的查询作为临时结果,再对它做 WHERE rn = 1。结合 LEFT JOIN 时,必须确保子查询已按分组字段和排序逻辑完整定义,否则第一条可能不稳定。
常见错误场景:主表有 10 条记录,关联的子查询没加 PARTITION BY main_id,导致所有行被当成一组编号;或者没写 ORDER BY,ROW_NUMBER() 结果不可预测。
- 分组字段必须和
LEFT JOIN关联键一致(如PARTITION BY t1.id) - 排序字段决定“第一条”是谁,例如按
created_at DESC取最新一条 - 子查询别名必须声明,否则外层无法引用
rn
示例:
SELECT t1.*, t2.name, t2.status
FROM orders t1
LEFT JOIN (
SELECT *,
ROW_NUMBER() OVER (PARTITION BY order_id ORDER BY updated_at DESC) AS rn
FROM order_logs
) t2 ON t1.id = t2.order_id AND t2.rn = 1;
LEFT JOIN + ROW_NUMBER 后 NULL 值消失?
这是最容易踩的坑:把 t2.rn = 1 写在 ON 条件里是对的,但如果误写成 WHERE t2.rn = 1,就会把主表没有匹配记录的行整个过滤掉,LEFT JOIN 变成 INNER JOIN。
-
ON t1.id = t2.order_id AND t2.rn = 1✅ 保留主表所有行,没匹配则t2.*全为 NULL -
WHERE t2.rn = 1❌ 过滤掉所有t2为 NULL 的行,失去 LEFT 特性 - 如果子查询本身没数据,
t2.rn根本不存在,所以WHERE判定直接失效
性能和兼容性要注意什么?
ROW_NUMBER() 在 MySQL 8.0+、PostgreSQL、SQL Server、Oracle 都支持,但 SQLite 不支持(需改用 correlated subquery)。性能上,关键看 PARTITION BY 和 ORDER BY 字段是否有索引。
- 复合索引推荐:(
order_id,updated_at),顺序不能反 - 大数据量时,避免在子查询里
SELECT *,只选真正需要的列 - 某些旧版 MySQL(
复杂点在于:分组逻辑和排序逻辑必须严格对应业务语义,“第一条”不是随便排出来的,而是你明确定义的业务规则。漏掉 PARTITION BY 或写错字段,结果就全偏了。










