其中列值已更改选择行其中列值已更改选择行(Select rows where column valu

比方说，我有如下表：

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提前。

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

我目前使用SQL Server 2008中，虽然我有机会到2012年，如果新的窗口/分析功能是有益的。

我试图适应的解决方案在这里http://blog.sqlauthority.com/2011/11/24/sql-server-solution-to-puzzle-simulate-lead-and-lag-without-using-sql-server-2012-解析函数/但我查询一个小时后没有完成！ 我认为，加入炸行大小的东西无法控制的（或者我搞砸了）。

我可以解决这个问题，C＃代码和多个数据库调用，但似乎喜欢的事，可在这将是好得多的表值函数或SP来完成。

此外，只有随着工作的解决方案Value是确定的，如果这是更容易。

Answer 1:

我觉得这是你追求的：

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

这可能是缓慢的，如果结果集大，没有一个很好的辅助指标...

编辑

啊，等一下，值有涨有跌，而不仅仅是起来......如果是这样的话，你可以试试这个慢得多的方法：

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

Answer 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

结果：

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

Answer 3:

我们可以做到这一点使用子查询也

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;

所以，我比较的2行中的值如果发生改变则RN的差异将不会等于1个，否则RN值将由1所以，我选择所有的，其差额与下一行的RN值的行增量不为1和sub2.rn IS NULL用于第一行，因为联接从ID = 2将发生。

文章来源: Select rows where column value has changed