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

（编辑：湖南网）

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