选择列值已更改的行 [英] Select rows where column value has changed

问题描述

假设我有下表:

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     

如何选择加星号的行，即Value的位置已更改?基本上，我试图查找Value更改的时间，以便我可以基于这些时间间隔进行其他查询.解决方案不应该依赖于预先知道Value或Time.

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.

在我看来，这应该不难(但显然对我来说已经足够难了！).

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

我目前正在使用SQL Server 2008，但是如果新的窗口/分析功能有帮助，我可以使用2012.

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

我尝试在此处调整解决方案

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).

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

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.

此外，如果更简单，则只能在增大Value的情况下使用的解决方案就可以了.

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

推荐答案

我认为这是您追求的目标

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...

编辑

啊，等一下，值会上升而不是上升，而是下降……如果是这种情况，您可以尝试这种慢得多的方法:

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;

结果:

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

这篇关于选择列值已更改的行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！