lateral比row_number()快，因其对每组单独执行带limit 2的子查询，利用(dept_id, salary)索引快速定位排序截断，仅读最多2行/组；而row_number()需全表扫描打序再过滤，io与排序开销大。
PostgreSQL里用LATERAL实现每组Top-2为什么比ROW_NUMBER()快
因为LATERAL对每个分组单独执行一次带LIMIT 2的子查询，数据库能利用(dept_id, salary)复合索引快速定位、排序、截断，实际只读取最多2行/组；而ROW_NUMBER()必须先扫描全表、为所有行打序号，再过滤，IO和排序开销都大得多。
常见错误是漏写LATERAL关键字，或子查询里直接写employees.dept_id = departments.dept_id却不给外层表加别名——PostgreSQL会报invalid reference to FROM-clause entry。
- 必须显式写
LATERAL (SELECT ...)，不能省略 - 外层列引用必须带别名，如
d.dept_id，不能用原始表名 - 若某部门只有1人，子查询返回1行，
AVG()仍正常计算，不会报错或补NULL - MySQL 8.0+也支持
LATERAL，但需确认优化器是否启用（SET optimizer_switch='derived_merge=off';有时必要）
SQL Server怎么写等效的Top-N分组查询
SQL Server不用LATERAL，而是用CROSS APPLY，语义完全一致，只是关键字不同。它同样支持按外层每行动态执行子查询，并可利用索引避免全表扫描。
示例：每个部门取工资最高的2人并算平均值
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，否则结果无序 - 不能用
OFFSET 0 ROWS FETCH NEXT 2 ROWS ONLY替代TOP，SQL Server中CROSS APPLY不支持FETCH语法 - 若想保留部门无员工的情况，改用
OUTER APPLY，此时salary为NULL，AVG()会跳过该组（除非显式COALESCE(AVG(...), 0)）
什么时候该放弃LATERAL/CROSS APPLY，改用窗口函数
当你要取的是“每组前N条完整行”，而不是聚合值（比如要查出姓名、ID、薪资三列），且N较大（如Top-20）、分组数较少时，ROW_NUMBER()更合适——LATERAL展开后结果集可能爆炸，JOIN代价高。
典型场景：
- 导出每个品类销量前10的产品明细（含图片URL、库存等多字段）
- 需要在结果中同时显示排名和累计占比（得用
SUM() OVER()配合ROW_NUMBER()） - 数据库不支持LATERAL（如旧版MySQL 5.7）或优化器对LATERAL支持不稳
关键区别：
ROW_NUMBER()是一次性扫描+全局排序，适合“保行不保性能”；LATERAL是多次小查询，适合“保性能不保写法简洁”。没有银弹，得看EXPLAIN ANALYZE里实际走的是Index Scan还是Seq Scan。
动态Top-N（每组N不同）怎么安全实现
如果dept_top_config表里存着每个部门对应的top_n值，就不能硬写LIMIT 2了。这时候必须用窗口函数，因为LATERAL子查询里不支持变量LIMIT（PostgreSQL报错ERROR: LIMIT must be an integer）。
正确做法是CTE先关联配置，再用ROW_NUMBER()打标，最后用rn 过滤：
WITH ranked AS (
SELECT e.dept_id, e.name, e.salary,
ROW_NUMBER() OVER (PARTITION BY e.dept_id ORDER BY e.salary DESC) AS rn
FROM employees e
), config AS (
SELECT dept_id, top_n FROM dept_top_config
)
SELECT r.dept_id, r.name, r.salary
FROM ranked r
INNER JOIN config c ON r.dept_id = c.dept_id
WHERE r.rn
<p>容易踩的坑：</p>
- 别在
WHERE里直接写(SELECT top_n FROM dept_top_config WHERE dept_id = e.dept_id)——这又变回相关子查询，O(n²)复杂度 - 确保
dept_top_config.dept_id有主键或唯一索引，否则JOIN可能产生笛卡尔积 - 如果某部门在
dept_top_config里没配置，INNER JOIN会丢数据；需要保留就改LEFT JOIN，再用COALESCE(c.top_n, 3)设默认值
真正难的不是语法，是判断哪条路径在你的数据分布、索引状态和并发压力下实际更快——得跑EXPLAIN (ANALYZE, BUFFERS)看真实IO和排序节点耗时，而不是凭经验猜。
