Oracle SQL使用RANK窗口函数筛选每个skul最新收货记录方法

窗口函数的计算时机晚于WHERE子句执行，你没法直接在当前查询层级加WHERE条件筛选排名值，正确写法是把带窗口函数的逻辑包一层子查询或者CTE，在外层做筛选。

• 如果你需要保留同一个skul在最新收货日当天的所有收货记录，保留RANK()即可；如果每个skul只需要返回1条记录，可以把RANK()替换成ROW_NUMBER()。

参考可直接运行的SQL（CTE写法）：

WITH receipt_rank AS (
    SELECT 
        branch||item AS skul,
        date,
        order_num,
        RANK() OVER (
            PARTITION BY branch||item
            ORDER BY date DESC
        ) AS rk
    FROM receipts
)
SELECT skul, date, order_num
FROM receipt_rank
WHERE rk = 1;

如果不想用CTE，用嵌套子查询写法逻辑完全一致：

SELECT skul, date, order_num
FROM (
    SELECT 
        branch||item AS skul,
        date,
        order_num,
        RANK() OVER (
            PARTITION BY branch||item
            ORDER BY date DESC
        ) AS rk
    FROM receipts
) t
WHERE rk = 1;

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