如何行号差异的方法 [英] how to difference of row numbers approach

问题描述

我有这个表格的数据

ItemId  Value   Date
1        2      2017-12-18 17:00:00.000
1        2      2017-12-18 17:02:00.000
1        2      2017-12-18 17:04:00.000
1        3      2017-12-18 17:06:00.000
1        3      2017-12-18 17:08:00.000
1        2      2017-12-18 17:10:00.000
1        2      2017-12-18 17:12:00.000
1        2      2017-12-18 17:16:00.000
1        4      2017-12-18 17:14:00.000

我想在sql server中这样输出

i want to output like this in sql server

ItemId     Value   MaxDate
1          2       2017-12-18 17:04:00.000
1          3       2017-12-18 17:08:00.000
1          2       2017-12-18 17:16:00.000
1          4       2017-12-18 17:14:00.000

感谢您的回答.

推荐答案

您似乎想要 value 更改之前的最后一行，尽管我不确定值4"从何而来(我的最好的猜测是最后一个输入行应该有一个4"和一个不同的时间戳).

You appear to want the last row before value changes, although I'm not sure where value "4" comes from (my best guess is that the last input row should have a "4" and a different timestamp).

如果是这样，你可以简单地使用lead():

If so, you can simply use lead():

select t.*
from (select t.*,
             lead(value) over (partition by itemId order by date) as next_value
      from t
     ) t
where next_value is null or next_value <> value;

这篇关于如何行号差异的方法的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！