子查询能替代ROW_NUMBER()、RANK()、MAX() OVER、COUNT() OVER等分析函数，但仅限于等值分组+单行取值场景；累计计算、滑动窗口等必须用分析函数。
子查询能替代哪些分析函数？
不是所有分析函数都能被子查询干净替代，但常见场景里，ROW_NUMBER()、RANK()、MAX() OVER、COUNT() OVER 这几类在等值分组+单行取值时，用关联子查询或 EXISTS + 聚合子查询往往更可控。比如你要取每个部门工资最高的员工，用 MAX(sal) OVER(PARTITION BY deptno) 是可行的，但若后续要加“仅取一条且按 empno 排序”，分析函数就得嵌套两层；而用子查询可一步到位：
-
SELECT *从主表emp a - 用
(SELECT MAX(sal) FROM emp b WHERE b.deptno = a.deptno)做标量子查询，过滤条件直接写在WHERE - 若需去重+排序逻辑，改用
NOT EXISTS比IN更稳，避免空值陷阱
为什么子查询重写有时比分析函数快？
关键在执行路径是否触发排序（SORT ORDER BY）和窗口缓冲（WINDOW SORT）。分析函数强制 Oracle 在内存中维护分区状态，当分区数据量大或分区数多时，容易 spill 到临时表空间；而等价子查询若能命中索引，可能走 NESTED LOOPS + INDEX RANGE SCAN，全程不排序。
- 例如：查“每个 product_code 下 global_version 最大的记录”，用
ROW_NUMBER() OVER(PARTITION BY product_code ORDER BY global_version DESC)会强制扫描全分区并排序；但改写为WHERE global_version = (SELECT MAX(global_version) FROM t2 WHERE t2.product_code = t1.product_code)，只要(product_code, global_version)有复合索引，就可能跳过排序 - 注意：子查询版本对统计信息更敏感，
NULL值存在时，MAX()返回NULL，主表对应行会被丢弃，这点和ROW_NUMBER()行为不同
子查询重写的三个易错点
实际跑不通，八成卡在这三处：
-
SELECT中用了子查询，但WHERE条件里又引用了子查询结果——Oracle 不允许在同一个层级里循环依赖，必须包一层内联视图 - 多列比较时没用元组语法，比如想查 “(a,b) 同时等于子查询返回的 (x,y)” ，写成
WHERE a = (SELECT x ...) AND b = (SELECT y ...)是错的，应写成WHERE (a,b) IN (SELECT x,y FROM ...) - 子查询返回多行却用于标量上下文，报错
ORA-01427: single-row subquery returns more than one row；此时要么加ROWNUM = 1，要么确认业务是否真允许多值，改用IN或EXISTS
什么时候坚决别用子查询替代？
涉及累计计算（如 SUM() OVER(ORDER BY ... ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)）、滑动窗口（AVG() OVER(... ROWS BETWEEN 2 PRECEDING AND 2 FOLLOWING)）、或需要保留原始行顺序但又跨分区引用的场景，子查询无法自然表达。强行拆解会导致 N² 复杂度——比如每行都跑一次全表扫描找前5条，性能反而雪崩。
这类逻辑必须留在分析函数里，优化方向是调索引（确保 ORDER BY 列有高效访问路径）或控制分区粒度（避免单个 PARTITION BY 值下数据过载）。
