基于年周与日期对比更新目标表的MERGE语句优化问询

现有两张结构相似的SQL Server表：[dbo].[srcTable]（源表）和[dbo].[destTable]（目标表），表结构及初始数据如下：

表结构 #

CREATE TABLE [dbo].[srcTable](
    [Id] [INT] NULL,
    [Value] [INT] NULL,
    [QueryDate] [DATE] NULL
) 

CREATE TABLE [dbo].[destTable](
    [Id] [INT] NULL,
    [Value] [INT] NULL,
    [QueryDate] [DATE] NULL
) 

初始数据 #

-- 源表数据
TRUNCATE TABLE [dbo].[srcTable]
INSERT INTO [dbo].[srcTable]
(
    [Id],
    [Value],
    [QueryDate]
)
SELECT 2, 3, '2023-01-03'
UNION ALL
SELECT 2, 3, '2023-03-03'
UNION ALL
SELECT 3, 4, '2023-03-02'
UNION ALL
SELECT 5, 6, '2023-03-04'
UNION ALL
SELECT 3, 4, '2023-03-17'

-- 目标表数据
TRUNCATE TABLE [dbo].[destTable]
INSERT INTO [dbo].[destTable]
(
    [Id],
    [Value],
    [QueryDate]
)
SELECT 1, 2, '2023-03-03'
UNION ALL
SELECT 1, 2, '2023-03-10'

同步需求 #

需要始终让[dbo].[destTable]保留最新的QueryDate记录，规则如下：

• 若源表中存在与目标表**年周（YEAR-WEEK）**匹配的记录，且源表该年周的QueryDate大于等于目标表对应年周的QueryDate，则删除目标表该年周的所有记录，并插入源表对应年周的记录；
• 若目标表中无对应年周的记录，则直接插入源表该年周的记录。

尝试用MERGE语句实现时，无法在匹配分支同时完成删除与插入操作，分步删除插入又较为繁琐，询问是否有更优的MERGE实现方式。

解决方案 #

MERGE语句本身在匹配分支仅支持UPDATE/DELETE操作，无法直接同时完成删除后插入，但可以通过先聚合年周的最大日期做判断，再在MERGE中拆分删除和插入逻辑实现需求：

WITH srcYearWeek AS (
    -- 聚合源表每个年周的最大日期，同时保留对应记录字段
    SELECT 
        Id,
        Value,
        QueryDate,
        YEAR(QueryDate) AS YearNum,
        DATEPART(WEEK, QueryDate) AS WeekNum,
        MAX(QueryDate) OVER (PARTITION BY YEAR(QueryDate), DATEPART(WEEK, QueryDate)) AS MaxWeekDate
    FROM [dbo].[srcTable]
),
destYearWeek AS (
    -- 聚合目标表每个年周的最大日期
    SELECT 
        YEAR(QueryDate) AS YearNum,
        DATEPART(WEEK, QueryDate) AS WeekNum,
        MAX(QueryDate) AS MaxWeekDate
    FROM [dbo].[destTable]
    GROUP BY YEAR(QueryDate), DATEPART(WEEK, QueryDate)
)
MERGE INTO [dbo].[destTable] AS Target
USING (
    -- 筛选出需要同步的源表记录：目标表无对应年周，或源表年周最大日期≥目标表对应年周最大日期
    SELECT s.Id, s.Value, s.QueryDate, s.YearNum, s.WeekNum
    FROM srcYearWeek s
    LEFT JOIN destYearWeek d ON s.YearNum = d.YearNum AND s.WeekNum = d.WeekNum
    WHERE d.YearNum IS NULL OR s.MaxWeekDate >= d.MaxWeekDate
) AS Source 
-- 关联条件：匹配目标表中年周与源表需要同步的年周一致的记录
ON YEAR(Target.QueryDate) = Source.YearNum AND DATEPART(WEEK, Target.QueryDate) = Source.WeekNum

-- 删除目标表中需要被替换的年周记录
WHEN MATCHED THEN
    DELETE

-- 插入源表中需要同步的记录（包括目标表原本没有的年周，以及刚删除的年周的新记录）
WHEN NOT MATCHED THEN
    INSERT (Id, Value, QueryDate)
    VALUES (Source.Id, Source.Value, Source.QueryDate);

说明 #

1. 先通过CTE聚合源表和目标表的年周最大日期，用于判断是否需要替换目标表的年周数据；
2. MERGE的关联条件定位目标表中需要被替换的年周记录，执行删除；
3. 未匹配的源表记录（即目标表无对应年周，或刚删除年周的新数据）直接插入目标表；
4. 若源表同一年周存在多条记录，需确保业务上这些记录的Id/Value属性一致，或调整聚合逻辑（比如取最大日期对应的单条记录）。

如果更倾向于直观的分步操作，也可以用以下方式实现，性能与MERGE差异不大：

-- 第一步：删除目标表中需要替换的年周记录
DELETE d
FROM [dbo].[destTable] d
JOIN (
    SELECT YEAR(s.QueryDate) AS YearNum, DATEPART(WEEK, s.QueryDate) AS WeekNum, MAX(s.QueryDate) AS MaxSrcDate
    FROM [dbo].[srcTable] s
    GROUP BY YEAR(s.QueryDate), DATEPART(WEEK, s.QueryDate)
) s ON YEAR(d.QueryDate) = s.YearNum AND DATEPART(WEEK, d.QueryDate) = s.WeekNum
JOIN (
    SELECT YEAR(d.QueryDate) AS YearNum, DATEPART(WEEK, d.QueryDate) AS WeekNum, MAX(d.QueryDate) AS MaxDestDate
    FROM [dbo].[destTable] d
    GROUP BY YEAR(d.QueryDate), DATEPART(WEEK, d.QueryDate)
) t ON YEAR(d.QueryDate) = t.YearNum AND DATEPART(WEEK, d.QueryDate) = t.WeekNum
WHERE s.MaxSrcDate >= t.MaxDestDate;

-- 第二步：插入源表中目标表没有的年周记录
INSERT INTO [dbo].[destTable] (Id, Value, QueryDate)
SELECT s.Id, s.Value, s.QueryDate
FROM [dbo].[srcTable] s
LEFT JOIN (
    SELECT YEAR(d.QueryDate) AS YearNum, DATEPART(WEEK, d.QueryDate) AS WeekNum
    FROM [dbo].[destTable] d
    GROUP BY YEAR(d.QueryDate), DATEPART(WEEK, d.QueryDate)
) d ON YEAR(s.QueryDate) = d.YearNum AND DATEPART(WEEK, s.QueryDate) = d.WeekNum
WHERE d.YearNum IS NULL;

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