Azure SQL Server中JOIN临时表时ROW_NUMBER()结果异常问题

首先，确保你在使用ROW_NUMBER()时的分区和排序逻辑完全符合需求——按订单项ID分区，先按交付量降序、再按交付日期降序排序，这样能保证每个订单项只保留交付量最大的最新行。

方法1：使用CTE生成临时表并关联 #

先通过CTE筛选出配送表的目标行，插入临时表后再和订单表关联：

-- 生成带序号的配送数据，筛选目标行插入临时表
WITH RankedDeliveries AS (
    SELECT 
        OrderItemID,
        DeliveryQuantity,
        DeliveryDate,
        -- 核心逻辑：按订单项分区，优先取交付量大的，量相同取最新的
        ROW_NUMBER() OVER (PARTITION BY OrderItemID ORDER BY DeliveryQuantity DESC, DeliveryDate DESC) AS RowNum
    FROM DeliveryTable
)
SELECT OrderItemID, DeliveryQuantity, DeliveryDate
INTO #TempMaxDeliveries
FROM RankedDeliveries
WHERE RowNum = 1;

-- 关联订单表核对交付情况
SELECT 
    o.OrderItemID,
    o.OrderQuantity,
    ISNULL(d.DeliveryQuantity, 0) AS DeliveredQuantity,
    d.DeliveryDate,
    CASE 
        WHEN ISNULL(d.DeliveryQuantity, 0) >= o.OrderQuantity THEN '已足额交付'
        WHEN d.DeliveryQuantity IS NULL THEN '未交付'
        ELSE '未足额交付' 
    END AS DeliveryStatus
FROM OrderTable o
LEFT JOIN #TempMaxDeliveries d 
    ON o.OrderItemID = d.OrderItemID;

方法2：直接关联子查询（无需临时表） #

如果不需要临时表存储中间结果，可以直接在关联时嵌入子查询：

SELECT 
    o.OrderItemID,
    o.OrderQuantity,
    ISNULL(d.DeliveryQuantity, 0) AS DeliveredQuantity,
    d.DeliveryDate,
    CASE 
        WHEN ISNULL(d.DeliveryQuantity, 0) >= o.OrderQuantity THEN '已足额交付'
        WHEN d.DeliveryQuantity IS NULL THEN '未交付'
        ELSE '未足额交付' 
    END AS DeliveryStatus
FROM OrderTable o
LEFT JOIN (
    SELECT 
        OrderItemID,
        DeliveryQuantity,
        DeliveryDate,
        ROW_NUMBER() OVER (PARTITION BY OrderItemID ORDER BY DeliveryQuantity DESC, DeliveryDate DESC) AS RowNum
    FROM DeliveryTable
) d 
    ON o.OrderItemID = d.OrderItemID 
    AND d.RowNum = 1;

你之前遇到问题的常见原因 #

1. 分区键错误：如果PARTITION BY指定的不是OrderItemID（比如误写为订单IDOrderID），会导致同一订单下的所有订单项被归为一组，生成的序号逻辑完全错误。
2. 排序逻辑缺失：只按交付日期排序，没优先按交付量降序，会取到最新但交付量小的行；或者排序方向搞反（用ASC而非DESC），导致目标行序号不是1。
3. 插入临时表时未筛选：如果直接将未过滤RowNum=1的CTE结果插入临时表，后续查询时又没加过滤条件，会看到全量数据，但你说插入后序号全变1，大概率是分区键写错了。

关于GROUP BY+MAX()的替代方案 #

如果要用GROUP BY，需要确保同时获取对应交付量的最新日期，避免出现“最大交付量对应旧日期”的情况，示例：

WITH MaxQtyPerItem AS (
    SELECT 
        OrderItemID,
        MAX(DeliveryQuantity) AS MaxDeliveryQuantity
    FROM DeliveryTable
    GROUP BY OrderItemID
),
LatestDeliveryForMaxQty AS (
    SELECT 
        d.OrderItemID,
        d.DeliveryQuantity,
        MAX(d.DeliveryDate) AS LatestDeliveryDate
    FROM DeliveryTable d
    JOIN MaxQtyPerItem m 
        ON d.OrderItemID = m.OrderItemID 
        AND d.DeliveryQuantity = m.MaxDeliveryQuantity
    GROUP BY d.OrderItemID, d.DeliveryQuantity
)
SELECT 
    o.OrderItemID,
    o.OrderQuantity,
    m.DeliveryQuantity,
    m.LatestDeliveryDate,
    CASE 
        WHEN m.DeliveryQuantity >= o.OrderQuantity THEN '已足额交付'
        ELSE '未足额交付' 
    END AS DeliveryStatus
FROM OrderTable o
LEFT JOIN LatestDeliveryForMaxQty m 
    ON o.OrderItemID = m.OrderItemID;

这种方法步骤更多，不如窗口函数直观，优先推荐窗口函数方案。

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