MS SQL Set ID группы без зацикливания

Question

Я хотел бы создать запрос в MS-SQL, чтобы сделать столбец, содержащий увеличивающийся номер группы.

Вот как я хочу, чтобы мои данные возвращались:

Column 1 | Column 2 | Column 3
------------------------------
    I    |     1    |     1
    O    |     2    |     2
    O    |     2    |     3
    I    |     3    |     4
    O    |     4    |     5
    O    |     4    |     6
    O    |     4    |     7
    O    |     4    |     8
    I    |     5    |     9
    O    |     6    |    10

• Column 1 это I а также O смысл в и вне.
• Column 2 группа строк (это должно увеличиваться, когда Column 1 изменения).
• Column 3 это номер строки.

Так как я могу написать свой запрос так, чтобы Column 2 увеличивается каждый раз Column 1 изменения?

Answer 1

Во-первых, для выполнения операций такого типа необходим столбец, который может определять порядок строк. Если у вас есть столбец, который определяет этот порядок, например столбец идентификаторов, его можно использовать для выполнения чего-то подобного:

Runnable образец:

CREATE TABLE #Groups
    (
      id INT IDENTITY(1, 1) , -- added identity to provide order
      Column1 VARCHAR(1)
    )

INSERT  INTO #Groups
        ( Column1 )
VALUES  ( 'I' ),
        ( 'O' ),
        ( 'O' ),
        ( 'I' ),
        ( 'O' ),
        ( 'O' ),
        ( 'O' ),
        ( 'O' ),
        ( 'I' ),
        ( 'O' );

;
WITH    cte
          AS ( SELECT   id ,
                        Column1 ,
                        1 AS Column2
               FROM     #Groups
               WHERE    id = 1
               UNION ALL
               SELECT   g.id ,
                        g.Column1 ,
                        CASE WHEN g.Column1 = cte.Column1 THEN cte.Column2
                             ELSE cte.Column2 + 1
                        END AS Column2
               FROM     #Groups g
                        INNER JOIN cte ON cte.id + 1 = g.id
             )
    SELECT  *
    FROM    cte
    OPTION (MAXRECURSION 0) -- required to allow for more than 100 recursions

DROP TABLE #Groups

Этот код эффективно просматривает записи, сравнивая каждую строку со следующей и увеличивая значение Column2 если значение в Column1 изменения.

Если у вас нет столбца идентификаторов, вы можете добавить его.

Кредит @AeroX:

С 30K записей, последняя строка: OPTION (MAXRECURSION 0) требуется переопределить значение по умолчанию 100 рекурсий при использовании общего выражения таблицы (CTE). Установка его в 0, означает, что оно не ограничено.

Answer 2

Это будет работать, если у вас есть sqlserver 2012+

DECLARE @t table(col1 char(1), col3 int identity(1,1))

INSERT @t values
('I'), ('O'), ('O'), ('I'), ('O'), ('O'), ('O'), ('O'), ('I'), ('O')

;WITH CTE AS
(
  SELECT 
    case when lag(col1) over (order by col3) = col1 
         then 0 else 1 end increase, 
    col1,
    col3
  FROM @t
)
SELECT
  col1,
  sum(increase) over (order by col3) col2,
  col3
FROM CTE

Результат:

col1  col2  col3
I     1     1
O     2     2
O     2     3
I     3     4
O     4     5
O     4     6
O     4     7
O     4     8
I     5     9
O     6     10