我有一个具有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最后一次改变.)
解决方法
这两个查询依赖于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;
更新
对于那些跟踪的人来说,如果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;
