Group by numbers that are in sequence

问题:

I have some data like this:

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

... 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.

回答1:

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

回答2:

with
  data(row, id) as (
    select * 
    from (
      values
       (1,1)
      ,(2,36)
      ,(3,37)
      ,(4,38)
      ,(5,50)
      ,(6,51)
    ) as foo(row, id)
  ),
  anchor(row, id) as (
    select row, id
    from data d1
    where not exists(select 0 from data d2 where d2.id = d1.id - 1)
  )
select d1.*, dense_rank() over(order by foo.id) as thegroup
from
  data d1
  cross apply (select max(id) from anchor where anchor.id <= d1.id) as foo(id)
order by
  d1.row
  ;

回答3:

;WITH firstrows AS
(
    SELECT id, ROW_NUMBER() OVER (ORDER BY id) groupid
    FROM Table1 a
    WHERE id - 1 NOT IN (SELECT b.id FROM Table1 b)
)
SELECT id, 
    (
        SELECT MAX(b.groupid)
        FROM firstrows b
        WHERE b.id <= a.id
    ) groupid
FROM Table1 a

回答4:

This solution does more work that is strictly necessary on the basis that there may be gaps in the sequence of row values, and on the assumption that those gaps should be ignored.

Set up test data:

DECLARE @table TABLE
(ROW INT,
id INT
)

INSERT @table
SELECT 1,1
UNION SELECT 2,36
UNION SELECT 3,37
UNION SELECT 4,38
UNION SELECT 5,50
UNION SELECT 6,51

Output query

;WITH grpCTE
AS
(
    SELECT ROW, id,
    ROW_NUMBER() OVER (ORDER BY ROW
                        ) AS rn
    FROM @table
)
,recCTE
AS
(
    SELECT ROW, id, rn, 1 AS grp
    FROM grpCTE
    WHERE rn = 1

    UNION ALL

    SELECT g.row, g.id, g.rn, CASE WHEN g.id = r.id + 1 THEN r.grp ELSE r.grp + 1 END AS grp
    FROM grpCTE AS g
    JOIN recCTE AS r
    ON g.rn = r.rn + 1
)
SELECT row, id, grp FROM recCTE

回答5:

 Select T.Id, T.Row, groupId as "Group", dr  FROM tbrows T
Left Outer Join
    (
    Select min(id) as groupId,DENSE_RANK() over( order by min(id)) as dr, MIN(row-id) as d, Sum(1) as s FROM tbrows
         Group BY (row-id)
    ) U
On (T.Id >= U.groupId) and (T.Id < U.groupId+U.s)
Order By T.Id