按顺序编号分组 [英] Group by numbers that are in sequence

问题描述

我有一些这样的数据:

row    id
1      1
2      36
3      37
4      38
5      50
6      51

我要查询它看起来像这样:

I would like to query it to look like this:

 row    id    group
 1      1     1
 2      36    2
 3      37    2
 4      38    2
 5      50    3
 6      51    3

...以便我可以按数字连续的方式按GROUP BY.

... so that I can GROUP BY where the numbers are consecutively sequential.

另外，循环/游标也是不可能的，因为我正在处理大量数据，谢谢.

Also, looping/cursoring is out of the question since I'm working with a pretty large set of data, thanks.

推荐答案

create table #temp
(
    IDUnique int Identity(1,1),
    ID int, 
    grp int
)


Insert into #temp(ID) Values(1)
Insert into #temp(ID) Values(36)
Insert into #temp(ID) Values(37)
Insert into #temp(ID) Values(38)
Insert into #temp(ID) Values(50)
Insert into #temp(ID) Values(51)

declare @IDUnique int
declare @PreviousUnique int
declare @ID int
declare @grp int
declare @Previous int
declare @Row int

DECLARE @getAccountID CURSOR SET @getAccountID = CURSOR FOR SELECT Row_Number() Over(Order by IDUnique) Row, IDUnique, ID  From #temp
OPEN @getAccountID
FETCH NEXT FROM @getAccountID INTO @Row, @IDUnique, @ID 
WHILE @@FETCH_STATUS = 0
BEGIN
    IF(@Row = 1)
    Begin
        update #temp set grp = 1 Where IDUnique = @IDUnique
        set @Previous = @ID
        set @grp = 1
    End
    Else If (@Previous + 1 = @ID)
    Begin
        update #temp set grp = @grp Where IDUnique = @IDUnique
        set @Previous = @ID
    End
    Else
    Begin
        set @Previous = @ID
        set @grp = @grp + 1
        update #temp set grp = @grp Where IDUnique = @IDUnique
    End
    FETCH NEXT FROM @getAccountID INTO @Row, @IDUnique, @ID
END
CLOSE @getAccountID
DEALLOCATE @getAccountID

Select * from #temp
Drop Table #temp

这篇关于按顺序编号分组的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！