MS SQL Server中如何基于item_number和warehouse返回子查询首个匹配订单号

以下是几种能满足你需求的T-SQL实现方式，专门解决「按item_number+warehouse分组，取每组最早delivery_date对应的order_number」的场景：

方法1：ROW_NUMBER()窗口函数（最通用） #

先给子表的每个分组按配送日期排序打标记，再关联主表取最早的订单：

SELECT 
    m.*,
    sq.earliest_order_number
FROM 
    main_table m
LEFT JOIN (
    SELECT 
        item_number,
        warehouse,
        order_number AS earliest_order_number,
        -- 按item+仓库分组，配送日期升序编号，最早的记录编号为1
        ROW_NUMBER() OVER (PARTITION BY item_number, warehouse ORDER BY delivery_date ASC) AS rn
    FROM 
        sub_table -- 替换为你的子查询或实际表
) sq 
    ON m.item_number = sq.item_number 
    AND m.warehouse = sq.warehouse 
    AND sq.rn = 1
ORDER BY 
    m.delivery_date; -- 保留主数据集原排序

ROW_NUMBER()会给每个item_number+warehouse组内的记录按配送日期升序编号，编号为1的就是最早的订单，关联后就能把订单号带到主数据集里。

方法2：APPLY子查询（更简洁） #

用OUTER APPLY为主表的每一行直接匹配对应分组的最早订单：

SELECT 
    m.*,
    sq.earliest_order_number
FROM 
    main_table m
OUTER APPLY (
    SELECT TOP 1 order_number AS earliest_order_number
    FROM sub_table st
    WHERE st.item_number = m.item_number AND st.warehouse = m.warehouse
    ORDER BY st.delivery_date ASC
) sq
ORDER BY m.delivery_date;

这种方式相当于为主表每行单独执行一次子查询，直接取TOP1的最早订单号，写法更简洁，适合只需要取单个值的场景。

方法3：修正你的FIRST_VALUE用法 #

你之前的FIRST_VALUE逻辑有误，正确写法不需要CASE判断，直接按分组取最早值：

SELECT 
    DISTINCT -- 主表和子表一对多关联时，需要DISTINCT避免主表行重复
    m.*,
    FIRST_VALUE(st.order_number) OVER (
        PARTITION BY m.item_number, m.warehouse 
        ORDER BY st.delivery_date ASC
        ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
    ) AS earliest_order_number
FROM 
    main_table m
LEFT JOIN sub_table st 
    ON m.item_number = st.item_number 
    AND m.warehouse = st.warehouse
ORDER BY 
    m.delivery_date;

如果主表和子表是一对多关联，必须加DISTINCT，否则主表每行都会重复显示同一个订单号。

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