时间戳

首页 > 解决方案 > 使用两个分别存储历史插入和删除的 SQL 表查找当前数据集

sql - 使用两个分别存储历史插入和删除的 SQL 表查找当前数据集

问题描述

问题

我需要每天将我们最新的内部数据同步到不提供更新界面的外部审计数据库。为了更新一些记录，我需要首先生成并发送一个删除文件来删除这些记录，然后是一个插入文件，其中包含相同但更新的记录。

一个重要的细节是删除文件中的所有记录必须逐字匹配外部记录，才能被删除。

建议的方法

目前我使用两个单独的 SQL 表来版本控制我插入/删除的内容。

假设现在inserted_records表格如下所示：

    id | file_version | contract_id | customer_name | start_year
    9  | 6            | 1           | Alice         | 2015
    10 | 6            | 2           | Bob           | 2015
    11 | 6            | 3           | Charlie       | 2015

deleted_records附有一个具有相同列的单独的空表。

现在，如果我想

inserted_records将生成两个新行，第 12 行和第 13 行，依次创建一个新的插入文件 7。

    id | file_version | contract_id | customer_name | start_year
    9  | 6            | 1           | Alice         | 2015
    10 | 6            | 2           | Bob           | 2015
    11 | 6            | 3           | Charlie       | 2015
    12 | 7            | 1           | Dave          | 2015
    13 | 7            | 2           | Bob           | 2020

然后将它们在第 9 行和第 10 行中的原始列值复制到先前为空deleted_records的 1 中，进而创建一个新的删除文件 1。

    id | file_version | contract_id | customer_name | start_year
    1  | 1            | 1           | Alice         | 2015
    2  | 1            | 2           | Bob           | 2015

现在，如果我先发送删除文件 1，然后发送插入文件 7，我会得到我想要的结果。

问题

考虑到所有已发生的插入和删除，如何查询当前记录集？假设所有记录deleted_records总是有匹配的inserted_records，如果有多个，我们总是首先删除文件版本号较小的记录。

我试过先写一个来查询按 .inserted_records分组的最新记录contract_id。

select top 1 with ties *
from insertion_record
order by row_number() over (partition by contract_id order by file_version desc)

这将给我第 11、12 和 13 行，这是我在这个特定示例中想要的。但是，如果我们还想删除 Charlie 的第 11 行记录，那么我的查询将不再起作用，因为它没有考虑deleted_records在内，而且我不知道如何在 SQL 中执行此操作。

此外，我的坚果告诉我，这种方法并不可靠，因为有两个独立的移动部件，也许有更好的方法来解决这个问题？

标签： sqlsql-server

解决方案

如何查询当前的记录集

我不明白你的问题。每个 SQL 查询都针对当前记录集，如果您指的是当前数据库中的数据。

我确实看到了几个问题。

  1. 除非您要从中删除的表定义了一个键，否则即使每一列的完全匹配也可能会删除多行。

  2. 您正在使用 UPDATE 的交易保证执行临时更新。我想您正在更新的表是空闲的，实际上您不必担心其他人（或您）在插入到达之前重新插入已删除的行。但这是等待发生的问题。

如果您尝试生成的行集将是一系列插入和删除的结果，那么您还没有提供足够的信息来说明如何做到这一点，或者即使它是可能的。必须有某种方法来唯一标识行，以便可以关联删除和插入。（毕竟，它们并非在所有列上都匹配。）并且您需要一些操作顺序的指示，因为 INSERT 是在 DELETE 之后还是之前很重要。

推荐阅读