如何使用IN子句筛选订单表中各订单号的最新年度记录？

现有订单表orders结构如下：

create table orders (
    order_number varchar(6) NOT NULL,
    order_year year NOT NULL,
    ...
    primary key (order_number, order_year)
);

表中数据示例：

A111,2023,...
A111,2020,...
A222,2021,...

需求是从指定的订单号集合中，仅获取每个订单号对应order_year最新的记录。以下是几种可行的修改方案：

方案1：使用窗口函数（推荐，适用于支持窗口函数的数据库） #

利用ROW_NUMBER()窗口函数按订单号分组，按年份降序排序后取每组第一条：

WITH ranked_orders AS (
    SELECT 
        *,
        ROW_NUMBER() OVER (PARTITION BY order_number ORDER BY order_year DESC) AS rn
    FROM orders
    WHERE order_number IN ('A111', 'A222', ...)
)
SELECT * FROM ranked_orders WHERE rn = 1;

• 原理：PARTITION BY order_number将数据按订单号分组，ORDER BY order_year DESC让每个组内最新年份的记录排在最前面，rn=1筛选出每组的第一条（即最新年度记录）。

方案2：子查询关联（兼容不支持窗口函数的旧版本数据库） #

先通过子查询获取每个指定订单号的最大年份，再关联原表获取完整记录：

SELECT o.*
FROM orders o
JOIN (
    SELECT order_number, MAX(order_year) AS latest_year
    FROM orders
    WHERE order_number IN ('A111', 'A222', ...)
    GROUP BY order_number
) AS latest 
ON o.order_number = latest.order_number AND o.order_year = latest.latest_year;

方案3：关联子查询筛选 #

在WHERE子句中通过子查询匹配当前订单号的最大年份：

SELECT *
FROM orders o
WHERE order_number IN ('A111', 'A222', ...)
AND order_year = (
    SELECT MAX(order_year)
    FROM orders
    WHERE order_number = o.order_number
);

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