自己解决了With D1 as ( Select *, Value + 0.2 TmpValue from #A ), D2 as( Select a.ID, a.Value, Isnull(Max(b.Value), a.Value) MaxValue From D1 a Left Join D1 b On b.Value Between a.Value and a.TmpValue Group by a.ID, a.Value ), D3 as( Select Min(Value) MinValue, MaxValue, Row_Number() Over(Order by MaxValue) GroupNo From D2 Group by MaxValue ) Select a.*, b.GroupNo From #A a, D3 b Where a.Value Between b.MinValue and MaxValue;
Select *, Value + 0.2 TmpValue from #A
),
D2 as(
Select a.ID, a.Value, Isnull(Max(b.Value), a.Value) MaxValue
From D1 a Left Join D1 b On b.Value Between a.Value and a.TmpValue
Group by a.ID, a.Value
),
D3 as(
Select Min(Value) MinValue, MaxValue, Row_Number() Over(Order by MaxValue) GroupNo
From D2
Group by MaxValue
)
Select a.*, b.GroupNo
From #A a, D3 b
Where a.Value Between b.MinValue and MaxValue;