Oracle SQL如何在同一查询中按分区rank过滤最新2条数据

错误原因 #

你遇到的ORA-00904: "S"."ACCOUNT_NO": invalid identifier错误，核心原因有三点：

1. 外层查询错误引用了子查询内部的表别名b和s——子查询执行后，外层只能访问子查询返回的列（如t.rank），无法直接调用内部表的别名。
2. 子查询中表名写错，原表是NG1_RR.MCD_CBTL_DATA和NG1_PSD_TMP.TMP_RMC_APP，你写成了ACCOUNT和APPLICATION，导致数据库无法识别表结构。
3. 子查询缺少表连接条件，旧版逗号连接需要在WHERE中指定关联逻辑，你遗漏后会产生笛卡尔积，进一步导致外层逻辑混乱。

正确实现方式 #

方式一：子查询过滤 #

将原查询完整封装为子查询，在外层通过rank字段过滤出前2条记录：

SELECT 
    account_combined,
    COLLATERAL_ID,
    LOAN_SIZE,
    CURRENT_BALANCE,
    processing_date,
    app_account_combined,
    APPLICATION_CREATED_DATE,
    APPLICATION_ID,
    DT_OUTCOME
FROM (
    SELECT 
        b.SORT_CODE||b.ACCOUNT_NO AS account_combined,
        COLLATERAL_ID,
        LOAN_SIZE,
        CURRENT_BALANCE,
        processing_date,
        s.SORT_CODE||s.ACCOUNT_NO AS app_account_combined,
        APPLICATION_CREATED_DATE,
        APPLICATION_ID,
        DT_OUTCOME,
        ROW_NUMBER() OVER (PARTITION BY b.SORT_CODE||b.ACCOUNT_NO ORDER BY APPLICATION_CREATED_DATE DESC) AS rank
    FROM NG1_RR.MCD_CBTL_DATA b
    JOIN NG1_PSD_TMP.TMP_RMC_APP s 
        ON b.SORT_CODE||b.ACCOUNT_NO = s.SORT_CODE||s.ACCOUNT_NO
    WHERE processing_date = '08-JAN-24'
) t
WHERE t.rank <= 2
ORDER BY LOAN_SIZE DESC;

方式二：CTE公用表达式（更易读） #

用CTE先计算出带排名的数据集，再过滤结果：

WITH ranked_data AS (
    SELECT 
        b.SORT_CODE||b.ACCOUNT_NO AS account_combined,
        COLLATERAL_ID,
        LOAN_SIZE,
        CURRENT_BALANCE,
        processing_date,
        s.SORT_CODE||s.ACCOUNT_NO AS app_account_combined,
        APPLICATION_CREATED_DATE,
        APPLICATION_ID,
        DT_OUTCOME,
        ROW_NUMBER() OVER (PARTITION BY b.SORT_CODE||b.ACCOUNT_NO ORDER BY APPLICATION_CREATED_DATE DESC) AS rank
    FROM NG1_RR.MCD_CBTL_DATA b
    JOIN NG1_PSD_TMP.TMP_RMC_APP s 
        ON b.SORT_CODE||b.ACCOUNT_NO = s.SORT_CODE||s.ACCOUNT_NO
    WHERE processing_date = '08-JAN-24'
)
SELECT 
    account_combined,
    COLLATERAL_ID,
    LOAN_SIZE,
    CURRENT_BALANCE,
    processing_date,
    app_account_combined,
    APPLICATION_CREATED_DATE,
    APPLICATION_ID,
    DT_OUTCOME
FROM ranked_data
WHERE rank <= 2
ORDER BY LOAN_SIZE DESC;

关键注意点 #

• 窗口函数（如ROW_NUMBER）的计算逻辑是在WHERE过滤之后执行的，因此必须将其放在子查询/CTE中，在外层完成排名过滤。
• 尽量使用JOIN...ON替代旧版逗号连接的写法，逻辑更清晰，避免遗漏关联条件。
• 子查询返回的列建议起别名，方便外层查询调用，同时提升代码可读性。

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