我正在嘗試使用 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
uj5u.com熱心網友回復：
此解決方案假定單個的 2 行SetName 不能具有相同的值Timestamp（2 行的不同值SetName可以）。
一種方法是使用間隙和孤島將值分組，然后從資料中省略組 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<>小提琴
如果 2 行可以具有相同的值，那么希望您也可以使用ORDER BY始終遞增的值；否則你無法知道“第一”行是什么。
uj5u.com熱心網友回復：
要跟蹤整個資料集的更改，您幾乎就在那里，您只需要使用洗掉第一行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:在這里試試
注意：從給定的資料集中，SetName 級別的修改記錄和整個資料集的修改記錄是相同的。
假設：每個 Timestamp 值都是不同的。
轉載請註明出處，本文鏈接：https://www.uj5u.com/net/481355.html
上一篇：T-SQL命令未進入CATCH
下一篇：CASE運算式中的轉換失敗