如何每日更新基于DIBBS_nsn创建的winner表（去重并保留最新记录）

我用下面的SQL从DIBBS_nsn表生成了winner表，用来存储所有order_status为'WON'的nsn_part_number及其reason，并且每个重复的nsn_part_number只保留date_accessed最新的那条记录：

SELECT *
INTO "winner"
FROM 
    (SELECT  
         nsn_part_number, 
         reason, 
         ROW_NUMBER() OVER(PARTITION BY nsn_part_number ORDER BY date_accessed DESC) rn
     FROM 
         DIBBS_nsn
     WHERE
         order_status = 'WON') a
WHERE rn = 1

现在DIBBS_nsn每天都会新增数据，我需要一个能每日执行的Upsert语句来自动更新winner表。之前试了下面的MERGE语句，但它没法过滤重复记录，目前只能临时删掉winner表再重建，希望能找到更好的方案：

MERGE INTO winner AS target
USING dibbs_nsn AS source ON (target.nsn_part_number = source.nsn_part_number)
                          AND (source.order_status = 'WON')

WHEN NOT MATCHED
    THEN INSERT (nsn_part_number, reason)
         VALUES (source.nsn_part_number, source.reason)

你需要先在MERGE的数据源中筛选出DIBBS_nsn里每个nsn_part_number的最新WON记录，再和winner表做合并，这样既能插入新的记录，也能更新已有编号的最新数据。

完整Upsert语句 #

MERGE INTO winner AS target
USING (
    -- 先筛选出DIBBS_nsn中每个nsn_part_number的最新WON记录
    SELECT 
        nsn_part_number, 
        reason,
        date_accessed
    FROM (
        SELECT  
            nsn_part_number, 
            reason,
            date_accessed,
            ROW_NUMBER() OVER(PARTITION BY nsn_part_number ORDER BY date_accessed DESC) rn
        FROM DIBBS_nsn
        WHERE order_status = 'WON'
    ) filtered_source
    WHERE rn = 1
) AS source 
ON target.nsn_part_number = source.nsn_part_number

-- 如果已存在该编号，且源数据的date_accessed更新，则替换reason和date_accessed
WHEN MATCHED AND source.date_accessed > target.date_accessed
    THEN UPDATE SET 
        reason = source.reason,
        date_accessed = source.date_accessed
-- 如果不存在该编号，则插入最新的WON记录
WHEN NOT MATCHED
    THEN INSERT (nsn_part_number, reason, date_accessed)
         VALUES (source.nsn_part_number, source.reason, source.date_accessed)

关键说明 #

1. date_accessed字段的必要性：winner表必须保留date_accessed字段，否则无法判断源数据是否有更新的记录。如果之前创建表时没加这个字段，先执行下面的语句添加（根据实际数据类型调整）：

   ALTER TABLE winner ADD COLUMN date_accessed DATETIME; -- 替换为你实际的日期类型，比如TIMESTAMP
   

2. 每日执行逻辑：每天运行上面的MERGE语句即可，它只会处理DIBBS_nsn中新增的或更新的WON记录，无需删除重建整个表。

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