mysql - 如何获取Value列中连续数据的开始和结束ID？

Question

您好，我的表中有以下数据:

我正在寻找以下输出，

我使用的是 SQL Server 2008 R2。我只是尝试使用 ID1 = ID2+1 和值不相等的条件自连接表。

Select    T1.ID as ID1 ,T1.Value V1 ,T2.ID as ID2, T2.Value as V2 From [Test_Seq] T1 
                INNER Join [Test_Seq] T2 
                 on T2.ID = T1.ID+1  
                 Where T1.Value <>T2.Value

它给了我以下输出，

我希望我能在这里得到更好的答案，并对这种情况有清晰的解释。

谢谢

Answer 1

您可以尝试使用此查询

;with cte as (
select *, row_number() over(partition by value order by id) as rn1,  row_number() over(order by id) rn from #startend
) , cte2 as (
select min(id) startid, max(id) as endid, value, rn-rn1 as r from cte
group by rn-rn1, value
) select startid, endid, value from cte2
order by startid