Oracle中取分区内最大rownum记录遇ORA-00923错误，求SQL修正帮助

我帮你拆解下问题根源，再给出可直接运行的修正方案：

错误核心原因 #

你遇到的ORA-00923本质是Oracle不允许在WHERE子句中直接引用窗口函数的别名（比如你定义的maxrownum1）。因为窗口函数的计算逻辑是在WHERE过滤之后才执行的，这时候WHERE子句根本识别不了这个别名。另外还有两个容易踩的小坑：

• 用Oracle原生伪列rownum作为分区排序依据，会导致结果不稳定——rownum是查询执行时临时生成的，没有固定顺序，建议换成表中实际的有序业务列（比如EFFDT）。
• 外层查询的rownum<=2000位置不合理，应该先过滤出每个分区的目标记录，再限制返回条数。

修正后的查询方案 #

这里给你两种可靠写法，按需选择：

方案1：用CTE（可读性更高，适合Oracle 11g+）

WITH name_partitioned AS (
    SELECT 
        id, 
        NAME_TYPE, 
        NAME, 
        EFFDT,
        -- 按id分区，用业务有序列（比如EFFDT）生成行号
        ROW_NUMBER() OVER (PARTITION BY id ORDER BY EFFDT DESC) AS rownum1,
        -- 直接统计分区内总行数（即最大行号），比max(rownum1)更高效
        COUNT(*) OVER (PARTITION BY id) AS maxrownum1
    FROM name_table
)
SELECT *
FROM name_partitioned
WHERE rownum1 = maxrownum1
-- 最后限制返回的总条数
AND ROWNUM <= 2000;

方案2：嵌套子查询（兼容旧版Oracle）

如果你的Oracle版本不支持CTE（11g之前），可以用嵌套子查询：

SELECT *
FROM (
    SELECT 
        id, 
        NAME_TYPE, 
        NAME, 
        EFFDT,
        ROW_NUMBER() OVER (PARTITION BY id ORDER BY EFFDT DESC) AS rownum1,
        COUNT(*) OVER (PARTITION BY id) AS maxrownum1
    FROM name_table
) a
WHERE a.rownum1 = a.maxrownum1
AND ROWNUM <= 2000;

关键优化说明 #

1. 稳定排序：把ORDER BY rownum ASC替换成ORDER BY EFFDT DESC（或你业务需要的有序列），确保每个分区的行号排序逻辑固定。
2. 高效计算最大行号：用COUNT(*) OVER (PARTITION BY id)代替max(rownum1)，无需额外计算行号最大值，直接统计分区行数，性能更优。
3. 符合Oracle执行逻辑：先在子查询/CTE中完成所有窗口函数计算，再在外部查询过滤目标记录，最后限制返回条数，完全适配Oracle的执行顺序。

内容的提问来源于stack exchange，提问作者devcoder112