使用T-SQL，只返回表中修改过的记录

Question

我试图使用T-SQL只返回表中修改过的记录。

这是我目前所做的:

BEGIN
IF OBJECT_ID('tempdb..#Test') IS NOT NULL
DROP TABLE #Test
CREATE TABLE #Test
(SetName nvarchar(100),
[Timestamp] datetime,
Value smallint)
INSERT INTO #Test VALUES('Alpha', GETDATE(), 1)
INSERT INTO #Test VALUES('Alpha', GETDATE(), 0)
INSERT INTO #Test VALUES('Alpha', GETDATE(), 1)
INSERT INTO #Test VALUES('Beta', GETDATE(), 1)
INSERT INTO #Test VALUES('Beta', GETDATE(), 1)
INSERT INTO #Test VALUES('Beta', GETDATE(), 1)
INSERT INTO #Test VALUES('Gamma', GETDATE(), 1)
INSERT INTO #Test VALUES('Gamma', GETDATE(), 0)
INSERT INTO #Test VALUES('Gamma', GETDATE(), 1)
SELECT * FROM #Test
END;

结果:

SetName Timestamp               Value
Alpha   2022-05-23 12:58:41.100 1
Alpha   2022-05-23 12:58:41.101 0
Alpha   2022-05-23 12:58:41.102 1
Beta    2022-05-23 12:58:41.103 1
Beta    2022-05-23 12:58:41.104 1
Beta    2022-05-23 12:58:41.105 1
Gamma   2022-05-23 12:58:41.106 1
Gamma   2022-05-23 12:58:41.107 0
Gamma   2022-05-23 12:58:41.108 1

预期结果:

Alpha   2022-05-23 12:58:41.101 0 -- Changed from 1 to 0
Alpha   2022-05-23 12:58:41.102 1 -- Changed from 0 to 1
Gamma   2022-05-23 12:58:41.107 0 -- Changed from 1 to 0
Gamma   2022-05-23 12:58:41.108 1 -- Changed from 0 to 1

下面的语句返回所有从0到1和从1到0的记录，我不明白为什么:

;WITH cte AS
(
SELECT 
SetName, [Timestamp], Value, lag(Value, 1, -1) OVER (ORDER BY [Timestamp]) AS LastValue
FROM #Test
) 
SELECT 
SetName, [Timestamp], [Value]
FROM
cte
WHERE value <> LastValue

Answer 1

此解决方案假设单个SetName的2行对于Timestamp不能具有相同的值(SetName的2行不同值是可以的)。

一种方法是使用空白和孤岛将值分组，然后从数据中省略组0:

WITH CTE AS(
SELECT SetName,
[Timestamp], --timestamp is a deprecrated synonym for rowversion; I don't recommend it's use as a column name
[value],
ROW_NUMBER() OVER (PARTITION BY SetName ORDER BY Timestamp) - 
ROW_NUMBER() OVER (PARTITION BY SetName, [Value] ORDER BY Timestamp) AS Grp
FROM #Test)
SELECT SetName,
[Timestamp], --timestamp is a deprecrated synonym for rowversion; I don't recommend it's use as a column name
[value]
FROM CTE
WHERE Grp != 0
ORDER BY [Timestamp];

,db&lt的在小提琴

如果2行可以具有相同的值，那么希望您可以在ORDER BY中使用一个始终递增的值;否则你无法知道"第一"是什么。行。

Answer 2

要跟踪整个数据集的变化，您几乎已经完成了，您只需要通过使用LastValue <> -1删除第一行。

WITH CTE AS
(
SELECT 
SetName, [Timestamp], Value, lag(Value, 1, -1) OVER (ORDER BY [Timestamp]) AS LastValue
FROM #Test
) 
SELECT 
SetName, [Timestamp], [Value]
FROM
CTE
WHERE value<>Lastvalue
AND Lastvalue<> -1; --Add this filter to remove the rows which doesn't have any Lag Value

跟踪SetName级别的变化。

WITH CTE AS
(
SELECT 
SetName, [Timestamp], Value, Lag(Value, 1, -1) OVER (PARTITION BY SetName ORDER BY [Timestamp]) AS LastValue
FROM #Test
) 
SELECT 
SetName, [Timestamp], [Value]
FROM
CTE
WHERE Value<>LastValue
AND LastValue <> -1;

db<>fiddle: Try here

注意:对于给定的数据集，SetName级别的修改记录和整个数据集的修改记录是相同的。

假设: