如何通过存储插入、删除历史的两个SQL表查询当前有效数据集

疑问1解答：查询当前有效记录集合 #

你可以通过CTE（公用表表达式）结合分组统计实现，核心逻辑是：统计每个contract_id对应的删除次数，再过滤掉插入表中对应序号小于等于删除次数的低版本记录，最终剩下的就是有效记录。
参考SQL如下（SQL Server语法，可根据你使用的数据库微调）：

WITH insert_ranked AS (
    -- 给每个合同的插入记录按版本从小到大排序编号
    SELECT 
        *,
        ROW_NUMBER() OVER (PARTITION BY contract_id ORDER BY file_version ASC) AS rn
    FROM inserted_records
),
delete_count AS (
    -- 统计每个合同的删除次数
    SELECT contract_id, COUNT(*) AS del_cnt
    FROM deleted_records
    GROUP BY contract_id
)
SELECT i.*
FROM insert_ranked i
LEFT JOIN delete_count d ON i.contract_id = d.contract_id
-- 没有删除记录的合同del_cnt为0，所有记录都保留；有删除记录的只保留编号大于删除次数的记录
WHERE i.rn > ISNULL(d.del_cnt, 0)

上述SQL可以覆盖你提到的删除Charlie的场景：如果删除id=11的记录，只需要在deleted_records中新增一条contract_id=3的记录，该合同的删除次数变为1，插入表中该合同只有1条排序为1的记录，1>1不成立，自然会被过滤，符合预期。

疑问2解答：更优的同步方案 #

你目前的双表方案确实存在数据一致性风险，比如插入记录操作和删除记录操作没有原子性保障，容易出现单边写入的问题。推荐使用单表全链路版本控制方案，具体设计如下：

• 只保留一张record_versions表，在原有字段基础上新增两个字段：
  • op_type：操作类型，枚举值为I（插入）、D（删除）
  • batch_version：操作批次版本号，同一次提交的删除+插入操作使用同一个批次号
• 每次同步时的操作逻辑：
  1. 对比内部最新数据和上一次同步的有效数据，梳理出需要更新/删除的记录
  2. 生成新的批次版本号
  3. 所有需要删除的旧记录，以op_type=D写入表中
  4. 所有需要新增的记录，以op_type=I写入表中
  5. 按批次号筛选op_type=D的记录生成删除文件，筛选op_type=I的记录生成插入文件，提交到外部数据库即可
     这个方案的优势：
• 所有变更轨迹存在同一张表，原子性更容易保障，不会出现双表数据不一致的问题
• 追溯更方便，同一个批次的变更可以直接通过批次号关联，排查问题更简单
• 查询有效记录的逻辑更简单，不需要跨表关联：按contract_id分组，取最新的一条记录，只要op_type=I就是有效记录，op_type=D说明该合同已被删除

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