SQL或LINQ:如何选择仅更改一个参数的记录? [英] SQL or LINQ: how do I select records where only one paramater changes?

问题描述

说我们有这个列表:

Id  IdRef   myColumn    anotherColumn
448    70      1            228
449    70      1            2s8
451    70      1            228
455    70      2            2a8
456    70      2            s28
457    70      2            28
458    70      3            v
459    70      3            28
460    70      4            22
461    70      3            54
462    70      4            45
463    70      3            s28

每次"myColumn"更改时，我都需要选择一个带有记录的列表.因此结果将是:

I need to select a list with a record everytime "myColumn" changes. So the result would be:

    Id  IdRef   myColumn    anotherColumn
448    70      1            228
455    70      2            2a8
458    70      3            v
460    70      4            22
461    70      3            54
462    70      4            45
463    70      3            s28

推荐答案

这是一个空白和孤岛的问题.在SQL中，这是使用窗口函数解决此问题的一种方法:

This is a gaps and islands problem. In SQL, here is one approach to solve it using window functions:

select Id, IdRef, myColumn, anotherColumn
from (
    select t.*, lag(myColumn) over(partition by IdRef order by Id) lagMyColumn
    from mytable t
) t
where lagMyColumn is null or lagMyColumn <> myColumn

内部查询恢复上一个行中的myColumn值，该值按Id排序.然后，外部查询会根据记录中的值与当前行中的值不同的记录进行过滤.

The inner query recovers the value of myColumn on the previous row, ordered by Id. Then the outer query filters on records where that value is different from the one on the current row.

DB Fiddle上的演示 :

 id | idref | mycolumn | anothercolumn
--: | ----: | -------: | :------------
448 |    70 |        1 | 228          
455 |    70 |        2 | 2a8          
458 |    70 |        3 | v            
460 |    70 |        4 | 22           
461 |    70 |        3 | 54           
462 |    70 |        4 | 45           
463 |    70 |        3 | s28          

这篇关于SQL或LINQ:如何选择仅更改一个参数的记录?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！