查询以获取对象图的所有修订

Question

我正在数据库上实现审核日志，因此所有内容都有 CreatedAt 和 RemovedAt 列。 现在我希望能够列出对象图的所有修订，但我能想到的最好方法是使用联合。 我需要获取每个唯一的CreatedAt 和RemovedAt id。

如果我得到一个有省份的国家列表，那么联盟看起来像这样：

SELECT c.CreatedAt AS RevisionId from Countries as c where localId=@Country
UNION
SELECT p.CreatedAt AS RevisionId from Provinces as p 
INNER JOIN Countries as c ON p.CountryId=c.LocalId AND c.LocalId = @Country
UNION
SELECT c.RemovedAt AS RevisionId from Countries as c where localId=@Country
UNION
SELECT p.RemovedAt AS RevisionId from Provinces as p 
INNER JOIN Countries as c ON p.CountryId=c.LocalId AND c.LocalId = @Country

对于更复杂的查询，这可能会变得非常复杂，并且可能表现得很差，所以我想看看是否有人能想到更好的方法。 这是在 MSSQL Server 中。

我需要将它们全部放在一个列表中，因为这是在 from 子句中使用的，而真正的数据来自于此的连接。

Answer 1

您很可能已经实施了您的解决方案，但要解决一些问题； 我建议考虑 Aleris 的解决方案或其某些衍生方案。

在您的表中，您有一个“删除于”字段 - 好吧，如果该字段处于活动状态（已填充），从技术上讲，数据不应该在那里 - 或者您的实现可能已将其标记为删除，这将破坏日志记录一旦它被删除。

当您在报告期间进行多次更新时会发生什么情况 - 以前的日志条目将被覆盖。

拥有单独的日志可以对日志信息进行归档，并允许您设置与更新/编辑周期不同的日志分析周期。

添加所需的任何“链接”字段，以便您能够返回原始源数据或者使描述足够详细。

日志中包含的字段由您决定，但 Aleris 的解决方案是直接的。 我可能会创建一个操作表并将字段类型从 varchar 更改为 int，作为操作表的链接——迫使开发人员执行一些标准化操作。

希望有帮助。

Answer 2

另一种方法是创建一个可能如下所示的审核日志：

AuditLog table
    EntityName varchar(2000),
    Action varchar(255),
    EntityId int,
    OccuranceDate datetime

其中 EntityName 是表的名称（例如：Contries、Provinces），Action 是审核操作（例如：Created、Removed 等），EntityId 是原始表中修改行的主键。

该表需要在对表执行的每个操作上保持同步。 有几种方法可以做到这一点：

1) 在每个表上创建触发器，将行添加到 AuditTable
2) 每次对相应表进行更改时，从应用程序中在 AuditTable 中添加行

使用此解决方案可以非常简单地获取审核中的日志列表。

如果您需要从原始表中获取列，也可以使用如下连接：

select *
from 
    Contries C 
    join AuditLog L on C.Id = L.EntityId and EntityName = 'Contries'

Answer 3

您可能可以通过交叉连接和合并来完成此操作，但从性能的角度来看，联合可能仍然更好。 不过，您可以尝试分别进行测试。

SELECT
     COALESCE(C.CreatedAt, P.CreatedAt)
FROM
     dbo.Countries C
FULL OUTER JOIN dbo.Provinces P ON
     1 = 0
WHERE
     C.LocalID = @Country OR
     P.LocalID = @Country