选择列值已更改的行

Let's say I have the following table:

假设我有下表：

Value    Time
0        15/06/2012 8:03:43 PM
1        15/06/2012 8:03:43 PM     *
1        15/06/2012 8:03:48 PM 
1        15/06/2012 8:03:53 PM
1        15/06/2012 8:03:58 PM     
2        15/06/2012 8:04:03 PM     *
2        15/06/2012 8:04:08 PM
3        15/06/2012 8:04:13 PM     *
3        15/06/2012 8:04:18 PM
3        15/06/2012 8:04:23 PM
2        15/06/2012 8:04:28 PM     *
2        15/06/2012 8:04:33 PM     

How do I select the starred rows, that is, where Value has changed? Basically I'm trying to find the time when Value has changed so I can do other queries based on those time intervals. The solution shouldn't depend on knowing Value or Time in advance.

如何选择已加星标的行，即Value已更改的位置？基本上我正在尝试找到Value已更改的时间，因此我可以根据这些时间间隔进行其他查询。解决方案不应该依赖于事先了解价值或时间。

It seems to me that this shouldn't be very hard (but it's hard enough for me apparently!).

在我看来，这应该不是很难（但显然对我来说很难！）。

I'm currently using SQL Server 2008 although I have access to 2012 if the new window/analytic functions are helpful.

我目前正在使用SQL Server 2008，但如果新窗口/分析功能有用，我可以访问2012。

I tried adapting the solutions here http://blog.sqlauthority.com/2011/11/24/sql-server-solution-to-puzzle-simulate-lead-and-lag-without-using-sql-server-2012-analytic-function/ but my query didn't complete after an hour! I think the joins explode the row size to something unmanageable (or I screwed it up).

我尝试在这里调整解决方案http://blog.sqlauthority.com/2011/11/24/sql-server-solution-to-puzzle-simulate-lead-and-lag-without-using-sql-server-2012-解析函数/但我的查询在一小时后没有完成！我认为连接将行大小爆炸到无法管理的东西（或者我搞砸了）。

I can solve this problem with C# code and multiple db calls, but it seems like something that could be done in a table-valued function or SP which would be much nicer.

我可以使用C＃代码和多个db调用来解决这个问题，但它似乎可以在表值函数或SP中完成，这样可以更好。

Also, a solution that only works with increasing Value is OK if that is easier.

此外，如果更容易，只有增加值的解决方案才行。

3 个解决方案

#1

I think this is what you're after:

我想这就是你所追求的：

;WITH x AS
(
  SELECT value, time, rn = ROW_NUMBER() OVER 
  (PARTITION BY Value ORDER BY Time)
  FROM dbo.table
)
SELECT * FROM x WHERE rn = 1;

This may be slow if the resultset is large and there isn't a good supporting index...

如果结果集很大并且没有良好的支持索引，这可能会很慢......

EDIT

编辑

Ah, wait a second, the values go up and down, not just up... if that is the case you can try this much slower approach:

啊，等一下，价值观上涨和下跌，而不仅仅是......如果是这种情况你可以尝试这么慢的方法：

DECLARE @x TABLE(value INT, [time] DATETIME)

INSERT @x VALUES
(0,'20120615 8:03:43 PM'),--
(1,'20120615 8:03:43 PM'),--*
(1,'20120615 8:03:48 PM'),--
(1,'20120615 8:03:53 PM'),--
(1,'20120615 8:03:58 PM'),--
(2,'20120615 8:04:03 PM'),--*
(2,'20120615 8:04:08 PM'),--
(3,'20120615 8:04:13 PM'),--*
(3,'20120615 8:04:18 PM'),--
(3,'20120615 8:04:23 PM'),--
(2,'20120615 8:04:28 PM'),--*
(2,'20120615 8:04:33 PM');

;WITH x AS
(
  SELECT *, rn = ROW_NUMBER() OVER (ORDER BY time)
  FROM @x
)
SELECT x.value, x.[time]
FROM x LEFT OUTER JOIN x AS y
ON x.rn = y.rn + 1
AND x.value <> y.value
WHERE y.value IS NOT NULL;

Results:

结果：

value  time
-----  -----------------------
1      2012-06-15 20:03:43.000
2      2012-06-15 20:04:03.000
3      2012-06-15 20:04:13.000
2      2012-06-15 20:04:28.000

#2

DECLARE @x TABLE(value INT, [time] DATETIME)

INSERT @x VALUES
(0,'20120615 8:03:43 PM'),--
(1,'20120615 8:03:43 PM'),--*
(1,'20120615 8:03:48 PM'),--
(1,'20120615 8:03:53 PM'),--
(1,'20120615 8:03:58 PM'),--
(2,'20120615 8:04:03 PM'),--*
(2,'20120615 8:04:08 PM'),--
(3,'20120615 8:04:13 PM'),--*
(3,'20120615 8:04:18 PM'),--
(3,'20120615 8:04:23 PM'),--
(2,'20120615 8:04:28 PM'),--*
(2,'20120615 8:04:33 PM');


; with temp as
(
SELECT 
    value, [time],  lag(value,1,-1) over (order by [time] ) as lastValue
FROM    @x
) 
SELECT 
    [value],[time] 
FROM 
    temp 
WHERE value <> lastValue

Results:

结果：

value   time
---------------------------
0   2012-06-15 20:03:43.000
1   2012-06-15 20:03:43.000
2   2012-06-15 20:04:03.000
3   2012-06-15 20:04:13.000
2   2012-06-15 20:04:28.000

#3

We can do this using sub queries also

我们也可以使用子查询来完成此操作

SELECT sub1.value, sub1.time FROM 
  (SELECT *,rn,id FROM 
     (SELECT *,row_number() over (partition by value order by time) AS rn, row_number() over (order by time) AS id FROM x ) order by time) sub1
  LEFT OUTER JOIN 
  (SELECT *,rn,id FROM 
     (SELECT *,row_number() over (partition by value order by time) AS rn, row_number() over (order by time) AS id FROM x ) order by time) sub2
  ON sub1.id = sub2.id + 1 
  WHERE sub1.rn - sub2.rn <> 1 OR sub2.rn IS NULL;

So, I have compared the values of 2 rows if it changes then the difference of rn will be not equal to 1 otherwise rn value will increment by 1 so, I have picked all the rows whose difference with next row's rn value is not 1 and sub2.rn IS NULL is used for the first row because the join will occur from id = 2.

所以，我已经比较了2行的值，如果它改变那么rn的差值将不等于1，否则rn值将增加1所以，我已经选择了与下一行的rn值不同的所有行，并且sub2.rn IS NULL用于第一行，因为连接将从id = 2开始。