LATERAL JOIN 实现 TopN 聚合比 ROW_NUMBER() 更高效，尤其在分组多、N 小且无复合索引时，它按需每组取前 N 行，避免全表扫描与冗余排序。
直接用 LATERAL JOIN 实现 TopN 聚合，比嵌套子查询快得多——尤其当分组多、N 小（如每类取前 3）、且底层表无合适复合索引时，LATERAL 能避免全表扫描+窗口函数的开销，让数据库按需拉取每组数据。
为什么 LATERAL JOIN 比 ROW_NUMBER() 更适合小 N 场景
当你要“每个部门取工资最高的 2 名员工并求其平均值”，ROW_NUMBER() 会先给全表打序号，再过滤；而 LATERAL JOIN 是对每个部门单独执行一次带 LIMIT 2 的子查询，实际只读取最多 2 行/部门。数据量越大、分组越多，性能差距越明显。
-
ROW_NUMBER()必须扫描全部employees行，即使你只要每组前 2 条 -
LATERAL可配合dept_id索引快速定位，再对单个部门内数据排序+截断 - PostgreSQL 和 SQL Server（
CROSS APPLY）原生支持；MySQL 8.0+ 也支持LATERAL，但需注意优化器是否启用
PostgreSQL 中 LATERAL JOIN 实现每组 TopN 聚合
假设表 employees(dept_id, name, salary)，目标：每个部门取最高薪的 2 人，计算其平均薪资。
SELECT d.dept_id, AVG(e.salary) AS avg_top2_salary FROM departments d LATERAL ( SELECT salary FROM employees e WHERE e.dept_id = d.dept_id ORDER BY e.salary DESC LIMIT 2 ) e GROUP BY d.dept_id;
- 必须用
LATERAL（不能省略），否则子查询无法引用外层d.dept_id -
ORDER BY + LIMIT在子查询内完成，数据库可利用(dept_id, salary)复合索引跳过排序 - 若某部门不足 2 人，
LATERAL自动返回少于 2 行，AVG()仍正常计算（不报错）
SQL Server 对应写法：CROSS APPLY 替代 LATERAL
语义完全等价，只是关键字不同：
SELECT d.dept_id, AVG(e.salary) AS avg_top2_salary FROM departments d CROSS APPLY ( SELECT TOP 2 salary FROM employees e WHERE e.dept_id = d.dept_id ORDER BY e.salary DESC ) e GROUP BY d.dept_id;
-
TOP 2必须配ORDER BY，否则结果不可控 - 执行计划中能看到 “Nested Loops + Top” 算子，说明是逐部门驱动取数
- 如果部门表
departments很小（比如就 10 个部门），这种写法几乎总是优于全表加ROW_NUMBER()
容易踩的坑和绕不开的约束
LATERAL / CROSS APPLY 看似简洁，但有硬性限制，忽略会导致逻辑错误或性能反转：
- 子查询中不能出现聚合函数（如
MAX(salary)），否则会报错 —— 因为LATERAL子查询是“每行驱动一次”，不是先聚合再关联 - 若想在子查询里用聚合（比如“取每个部门薪资中位数”），得先用 CTE 或子查询预聚合，再
LATERAL关联，不能混写 - PostgreSQL 中，
LATERAL子查询若含OFFSET（如分页取第 2–3 名），性能会陡降，因为无法跳过前 N 行；此时应改用ROW_NUMBER() - MySQL 8.0 默认禁用
LATERAL优化，需确认optimizer_switch包含lateral_derived=on
真正高效的前提，是明确知道 N 很小、分组键有索引、且不需要处理并列排名这类复杂语义——这时候 LATERAL 才是那个“少走弯路”的选择。一旦涉及并列、去重、或 N 动态来自配置表（如 dept_top_config），就得切回窗口函数路线，别硬套 LATERAL。
