sql-server – 尝试查找值上次更改的时间

我有一个具有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,'2012-07-01 00:00:02'),'2012-07-01 00:00:03'),'2012-07-01 00:00:04'),2,'2012-07-01 00:00:05'),'2012-07-01 00:00:06'),'2012-07-01 00:00:07'),'2012-07-01 00:00:08')

结果应该是：

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

(因为00:05是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,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
),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可以重复,那么就会发生争执.如果对于任何给定的Taco_ID,它可以从1变为2然后又变回1,则查询将不起作用.对于这种情况,这是一个解决方案,即使它不是那么大的差距.像Itzik Ben-Gan这样的人可能会想到岛屿技术,即使它与OP的情景无关 – 它可能与未来的读者有关.它有点复杂,我还添加了一个额外的变量 – 一个只有一个Taco_value的Taco_ID.

如果要包含任何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 *,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 examples

（编辑：源码网）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!