SymmetricDS：源端多行数据仅同步目标端同ID最新单行

嘿，这个场景在数据同步里太常见了！我给你分几种情况来拆解解决方案，你可以根据自己用的数据库、同步频率来选：

如果你的数据量不大，或者需要定期把源表的最新状态同步到目标表，用SQL的窗口函数+UPSERT逻辑就能搞定。核心思路是先找出每个ID对应的最新EffectiveDate记录，再同步到目标表（存在就更新，不存在就插入）。

通用SQL（支持MERGE的数据库：SQL Server、Oracle、PostgreSQL 15+） #

-- 先筛选出每个ID的最新记录
WITH latest_source_records AS (
    SELECT 
        ID, 
        EffectiveDate,
        -- 按ID分组，EffectiveDate倒序排序，最新的记录会被标记为1
        ROW_NUMBER() OVER (PARTITION BY ID ORDER BY EffectiveDate DESC) AS row_rank
    FROM 源表
)
-- 用MERGE做UPSERT操作
MERGE INTO 目标表 target
USING (SELECT ID, EffectiveDate FROM latest_source_records WHERE row_rank = 1) source
ON target.ID = source.ID
WHEN MATCHED THEN 
    UPDATE SET target.EffectiveDate = source.EffectiveDate  -- 匹配到就更新最新日期
WHEN NOT MATCHED THEN 
    INSERT (ID, EffectiveDate) VALUES (source.ID, source.EffectiveDate);  -- 没匹配到就插入

MySQL专属写法（MySQL不支持MERGE，用ON DUPLICATE KEY UPDATE） #

INSERT INTO 目标表 (ID, EffectiveDate)
SELECT ID, EffectiveDate
FROM (
    SELECT 
        ID, 
        EffectiveDate,
        ROW_NUMBER() OVER (PARTITION BY ID ORDER BY EffectiveDate DESC) AS row_rank
    FROM 源表
) AS ranked_records
WHERE row_rank = 1
ON DUPLICATE KEY UPDATE EffectiveDate = VALUES(EffectiveDate);

如果数据量很大，全量同步太耗资源，就用增量同步的方式：

• 准实时场景：借助CDC（变更数据捕获）工具（比如Debezium、Canal）捕获源表的新增/更新事件，在中间层对每个ID的事件做去重，只保留最新的一条，再同步到目标表做UPSERT。
• 定期增量场景：只处理上次同步时间之后的源表数据，再筛选每个ID的最新记录：

-- 假设@last_sync_time是上次同步的结束时间，比如'2024-01-01 00:00:00'
WITH incremental_latest AS (
    SELECT 
        ID, 
        EffectiveDate,
        ROW_NUMBER() OVER (PARTITION BY ID ORDER BY EffectiveDate DESC) AS row_rank
    FROM 源表
    WHERE EffectiveDate > @last_sync_time  -- 只取增量数据
)
MERGE INTO 目标表 target
USING (SELECT ID, EffectiveDate FROM incremental_latest WHERE row_rank = 1) source
ON target.ID = source.ID
WHEN MATCHED THEN 
    UPDATE SET target.EffectiveDate = source.EffectiveDate
WHEN NOT MATCHED THEN 
    INSERT (ID, EffectiveDate) VALUES (source.ID, source.EffectiveDate);

如果用ETL工具做同步，步骤更直观：

1. 从源表读取数据后，按ID分组；
2. 对每个分组内的记录按EffectiveDate降序排序，取第一条（也就是最新的记录）；
3. 用工具自带的UPSERT组件写入目标表，配置主键为ID，规则是：匹配到ID就更新，没匹配到就插入。

额外提醒 #

如果源表存在记录删除的情况，需要同步删除目标表对应ID的话，要在同步逻辑里额外处理：比如在MERGE语句中加上WHEN NOT MATCHED BY SOURCE THEN DELETE（部分数据库支持），或者通过CDC捕获删除事件同步到目标表执行删除。

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