SQLServer时态表能够在每次数据做更新，修改时备份当前记录到history表，这可以方便我们做数据追踪或数据恢复操作。时态表的具体创建查询可查看其他资料，这里介绍下如何使用时态表对比整理字段变化记录。
假设我们有一个std表，有id，name，age字段，使用下面script建立表及对应时态表。
CREATE TABLE [dbo].[Std] (
[ID] INT IDENTITY (1, 1) NOT NULL,
[Name] NVARCHAR (50) NULL,
[Age] INT NULL,
[RowStartTime] DATETIME2 (7) GENERATED ALWAYS AS ROW START NOT NULL,
[RowEndTime] DATETIME2 (7) GENERATED ALWAYS AS ROW END NOT NULL,
PRIMARY KEY CLUSTERED ([ID] ASC),
PERIOD FOR SYSTEM_TIME ([RowStartTime], [RowEndTime])
)
WITH (SYSTEM_VERSIONING = ON (HISTORY_TABLE=[dbo].[Std_History], DATA_CONSISTENCY_CHECK=ON));
1.我们插入两条测试数据
INSERT INTO [dbo].Std VALUES (‘ben’,30);
INSERT INTO [dbo].Std VALUES (‘leo’,30);
2.此时Std有两条记录，Std_History时态表无记录
3.我们更新ben为ben_new,物理删除leo的数据
UPDATE [dbo].[Std] SET Name=‘ben_new’ where Name=‘ben’;
DELETE FROM [dbo].[Std] WHERE Name=‘leo’;
4.此时Std有一条ben的记录，leo记录不存在。Std_History时态表有两条记录，因为修改或删除时时态表备份了原纪录。
5.现在我们希望能追溯出Std表字段的修改记录，包含修改时间，修改字段，原值，新值。新建记录显示NULL–>值，删除记录显示值–>NULL
以ben的记录为例：
id changetime field oldval newval
2 2022/4/1 name NULL ben
2 2022/4/1 age NULL 30
2 2022/4/2 name ben ben_new
以leo的记录为例：
id changetime field oldval newval
2 2022/4/1 name NULL leo
2 2022/4/1 age NULL 30
2 2022/4/2 name leo NULL
2 2022/4/2 age 30 NULL
解决方案
建立视图SQL：
CREATE VIEW std_changes
AS
WITH h AS (
SELECT *, 0 AS rn
FROM [dbo].[Std]
UNION ALL
SELECT *, ROW_NUMBER() OVER (PARTITION BY ID ORDER BY RowEndTime DESC, RowStartTime DESC)
FROM [dbo].[Std_History]
)
SELECT
ISNULL(old.ID, new.ID) AS ID
,ISNULL(old.RowEndTime, new.RowStartTime) AS ChangeTime
,CAST(ca.Field AS varchar(50)) AS Field
,ca.OldValue
,ca.NewValue
FROM h old
FULL JOIN h new ON old.ID = new.ID AND old.rn = new.rn + 1
CROSS APPLY (
VALUES
('Name', CAST(old.Name AS varchar(200)), CAST(new.Name AS varchar(200)))
,('Age', CAST(old.Age AS varchar(200)), CAST(new.Age AS varchar(200)))
) ca (Field, OldValue, NewValue)
WHERE (old.rn IS NULL OR old.rn <> 0)
AND ISNULL(OldValue,'') <> ISNULL(NewValue,'')
GO
测试结果：
