SQL子查询如何改写为窗口函数-SQL-PHP中文网

当子查询仅依赖当前行分组/排序上下文（如“查每个部门工资最高的员工”）时，宜用窗口函数替代；若含非相关where、exists或多表join聚合，则不宜强行窗口化。

sql子查询如何改写为窗口函数

当子查询出现在 SELECT 列表中且只依赖当前行的分组/排序上下文（比如“查每个部门工资最高的员工”），而没有跨多表关联或复杂条件过滤时，基本可以改写为窗口函数。反之，如果子查询含 WHERE 中的非相关条件、EXISTS、或涉及多表 JOIN 后聚合，强行窗口化反而更难维护。

常见错误是直接用 MAX(salary) 窗口函数去匹配原子查询逻辑，但原意常是“取某组内某排序下的第一条记录”，这时必须用排序+编号。

原写法（低效）：

SELECT name, dept, (SELECT salary FROM emp e2 WHERE e2.dept = e1.dept ORDER BY salary DESC LIMIT 1) AS top_salary FROM emp e1;

改写要点：用 ROW_NUMBER() OVER (PARTITION BY dept ORDER BY salary DESC) 标记每组最高薪者，再外层过滤 rn = 1
注意 RANK() 和 DENSE_RANK() 在并列时行为不同——若允许多个“最高薪”，应选 RANK()；若只要一个代表，必须用 ROW_NUMBER() 并加二级排序（如 ORDER BY salary DESC, id ASC）避免不确定性

比如“查每个订单与上一笔订单的时间差”，用子查询关联自身效率差，且易漏空值处理。

原写法常类似：

SELECT o1.order_id, o1.order_time - (SELECT MAX(o2.order_time) FROM orders o2 WHERE o2.customer_id = o1.customer_id AND o2.order_time

改写后直接：LAG(order_time) OVER (PARTITION BY customer_id ORDER BY order_time)，再做减法即可
必须显式指定 ORDER BY，否则结果无定义；默认 LAG(col, 1, NULL) 第三个参数是缺失时的填充值，不设会返回 NULL，影响计算
性能上，窗口函数一次扫描完成，子查询可能触发对每行的独立索引查找

像 (SELECT AVG(salary) FROM emp WHERE dept = e1.dept) 这种看似简单，改 AVG(salary) OVER (PARTITION BY dept) 虽语法正确，但容易忽略两个关键点：

原子查询若某部门无数据，结果为 NULL；窗口函数同理，但若误写成 AVG(salary) OVER ()（漏 PARTITION BY），就变成全表平均，语义完全错位
当主表有重复部门键、或存在 LEFT JOIN 导致某些行 salary 为 NULL，窗口聚合默认跳过 NULL，但子查询若没加 WHERE salary IS NOT NULL，行为可能不一致
若需严格对齐原逻辑，建议先用 CASE WHEN 过滤无效行，再套窗口函数，而不是依赖默认 NULL 处理