SQL Server 2014：关联表获取TOP 1日期的改写异常问题

我来帮你搞定这个SQL匹配的问题——你现在遇到的核心问题是：原来的单变量写法只能处理单条记录，改成表关联后没有正确为每条@tempFCPurgeOrder的记录匹配到唯一的、符合条件的最近历史记录，导致所有同Item+LOCN的记录都匹配到了最早的那条udt_LOTP_Purge记录。

问题根源分析 #

原写法用SET @ThepriorPurge = (SELECT TOP 1 ... ORDER BY TNP.Updated_Time DESC)，本质是针对单条固定条件（比如固定@destinationTank）取最近的符合条件的记录。但改成表关联时，如果不做分组排序，会把所有满足TNP.Updated_Time < tcp.updated_Time的记录都关联出来，而你需要的是每条tcp记录对应的最大（最近）的那个小于它的TNP.updated_Time。

解决方案：用窗口函数实现分组匹配 #

我们可以用ROW_NUMBER()窗口函数，为每条@tempFCPurgeOrder的记录单独分组，在分组内对匹配的udt_LOTP_Purge记录按时间倒序排名，取排名第一的就是我们要的最近历史记录。具体SQL如下：

WITH RankedPurgeRecords AS (
    SELECT 
        tcp.Item,
        tcp.updated_time AS Temp_Updated_Time,
        tcp.LOCN,
        TNP.updated_Time AS Prior_Purge_Time,
        -- 按temp表的每条唯一记录分组，对匹配的purge记录按时间倒序排名
        ROW_NUMBER() OVER (
            PARTITION BY tcp.Item, tcp.LOCN, tcp.updated_time 
            ORDER BY TNP.updated_Time DESC
        ) AS Purge_Rank
    FROM @tempFCPurgeOrder AS tcp
    LEFT JOIN [dbo].[udt_LOTP_Purge] AS TNP 
        ON tcp.Item = TNP.Item 
        AND TNP.LOCN = tcp.LOCN  -- 对应每条temp记录的LOCN，而非固定变量
        AND TNP.Updated_Time < tcp.updated_time
)
SELECT 
    Item,
    Temp_Updated_Time,
    LOCN,
    Prior_Purge_Time
FROM RankedPurgeRecords
WHERE Purge_Rank = 1;

代码解释 #

1. CTE关联表：先把两个表按Item、LOCN匹配，且筛选purge表时间早于temp表时间的记录；
2. 窗口函数分组排名：用PARTITION BY tcp.Item, tcp.LOCN, tcp.updated_time确保每条temp记录单独成组（如果你的temp表中Item+LOCN+updated_time是唯一的，这个分组条件就足够区分每条记录），然后按TNP.updated_Time DESC排序，让最近的历史记录排在每组的第一位；
3. 筛选目标记录：最后取Purge_Rank = 1的记录，就是每条temp记录对应的最近符合条件的purge时间。

匹配结果验证 #

按照你的期望规则，这段代码会输出：

• @tempFCPurgeOrder第1条（4732, 3/10/18 8:34, Tank1）→ 匹配udt_LOTP_Purge第1条（3/9/18 8:34）
• 第2条（4732, 3/11/18 15:36, Tank1）→ 匹配第2条（3/10/18 15:36）
• 第3条（4732, 3/12/18 12:39, Tank1）→ 匹配第2条（3/10/18 15:36）
• 第4条（4732, 3/14/18 9:19, Tank1）→ 匹配第3条（3/13/18 12:39）
• 第5条（7812, 3/7/18 14:42, Tank2）→ 匹配第5条（3/5/18 15:42）
• 第6条（7812, 3/19/18 14:35, Tank2）→ 匹配第6条（3/15/18 15:42）

完全符合你想要的匹配逻辑。

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