試圖找出值最後一次改變的時間

我有一個包含 ID、值和日期的表。此表中有許多 ID、值和日期。

記錄會定期插入到該表中。ID 將始終保持不變，但偶爾值會更改。

我如何編寫一個查詢，它會給我 ID 加上值更改的最近時間？注意：該值將始終增加。

從這個樣本數據：

 Create Table Taco
(  Taco_ID int,
   Taco_value int,
   Taco_date datetime)

Insert INTO Taco 
Values (1, 1, '2012-07-01 00:00:01'),
       (1, 1, '2012-07-01 00:00:02'),
       (1, 1, '2012-07-01 00:00:03'),
       (1, 1, '2012-07-01 00:00:04'),
       (1, 2, '2012-07-01 00:00:05'),
       (1, 2, '2012-07-01 00:00:06'),
       (1, 2, '2012-07-01 00:00:07'),
       (1, 2, '2012-07-01 00:00:08')

結果應該是：

Taco_ID      Taco_date
1            2012-07-01 00:00:05

（因為 00:05 是最後一次Taco_Value更改。）

Taco_value這兩個查詢依賴於始終隨時間增加的假設。

;WITH x AS
(
 SELECT Taco_ID, Taco_date,
   dr = ROW_NUMBER() OVER (PARTITION BY Taco_ID, Taco_Value ORDER BY Taco_date),
   qr = ROW_NUMBER() OVER (PARTITION BY Taco_ID ORDER BY Taco_date)
 FROM dbo.Taco
), y AS
(
 SELECT Taco_ID, Taco_date,
   rn = ROW_NUMBER() OVER (PARTITION BY Taco_ID, dr ORDER BY qr DESC)
 FROM x WHERE dr = 1
)
SELECT Taco_ID, Taco_date
FROM y 
WHERE rn = 1;

具有較少視窗函式瘋狂的替代方案：

;WITH x AS
(
 SELECT Taco_ID, Taco_value, Taco_date = MIN(Taco_date)
 FROM dbo.Taco
 GROUP BY Taco_ID, Taco_value
), y AS
(
 SELECT Taco_ID, Taco_date, 
   rn = ROW_NUMBER() OVER (PARTITION BY Taco_ID ORDER BY Taco_date DESC)
 FROM x
)
SELECT Taco_ID, Taco_date FROM y WHERE rn = 1;

SQLfiddle上的範例

---

更新

對於那些跟踪的人來說，如果Taco_value可以重複會發生什麼，則存在爭議。如果它可以從 1 變為 2，然後對於任何給定的 1 Taco_ID，則查詢將不起作用。這是針對這種情況的解決方案，即使它不是像 Itzik Ben-Gan 這樣的人可能能夠夢想的間隙和島嶼技術，即使它與 OP 的場景無關 - 它可能是與未來的讀者相關。它有點複雜，我還添加了一個額外的變數 - aTaco_ID只有一個Taco_value.

如果要包含任何 ID 的第一行，其中值在整個集合中根本沒有改變：

;WITH x AS
(
 SELECT *, rn = ROW_NUMBER() OVER 
   (PARTITION BY Taco_ID ORDER BY Taco_date DESC)
 FROM dbo.Taco
), rest AS (SELECT * FROM x WHERE rn > 1)
SELECT  
 main.Taco_ID, 
 Taco_date = MIN(CASE 
   WHEN main.Taco_value = rest.Taco_value 
   THEN rest.Taco_date ELSE main.Taco_date 
 END)
FROM x AS main LEFT OUTER JOIN rest
ON main.Taco_ID = rest.Taco_ID AND rest.rn > 1
WHERE main.rn = 1
AND NOT EXISTS 
(
 SELECT 1 FROM rest AS rest2
  WHERE Taco_ID = rest.Taco_ID
  AND rn < rest.rn
  AND Taco_value <> rest.Taco_value
) 
GROUP BY main.Taco_ID;

如果你想排除這些行，它會更複雜一些，但仍然是微小的變化：

;WITH x AS
(
 SELECT *, rn = ROW_NUMBER() OVER 
   (PARTITION BY Taco_ID ORDER BY Taco_date DESC)
 FROM dbo.Taco
), rest AS (SELECT * FROM x WHERE rn > 1)
SELECT 
 main.Taco_ID, 
 Taco_date = MIN(
 CASE 
   WHEN main.Taco_value = rest.Taco_value 
   THEN rest.Taco_date ELSE main.Taco_date 
 END)
FROM x AS main INNER JOIN rest -- ***** change this to INNER JOIN *****
ON main.Taco_ID = rest.Taco_ID AND rest.rn > 1
WHERE main.rn = 1
AND NOT EXISTS
(
 SELECT 1 FROM rest AS rest2
  WHERE Taco_ID = rest.Taco_ID
  AND rn < rest.rn
  AND Taco_value <> rest.Taco_value
)
AND EXISTS -- ***** add this EXISTS clause ***** 
(
 SELECT 1 FROM rest AS rest2
  WHERE Taco_ID = rest.Taco_ID
  AND Taco_value <> rest.Taco_value
)
GROUP BY main.Taco_ID;

更新了 SQLfiddle 範例

引用自：https://dba.stackexchange.com/questions/44784