Объединение данных в одну таблицу SQL без курсора

У меня есть таблица со столбцом ID и другой столбец с номером. Один идентификатор может иметь несколько номеров. Например

ID | Number
 1 |  25
 1 |  26
 1 |  30
 1 |  24
 2 |  4
 2 |  8
 2 |  5

Теперь, основываясь на этих данных, в новой таблице я хочу иметь этот

ID | Low | High 
1  |  24 |  26
1  |  30 |  30
2  |  4  |  5
2  |  8  |  8

Как вы можете видеть, я хочу объединить любые данные, где числа являются последовательными, например, 24, 25, 26. Таким образом, теперь низкий - 24, максимум - 26, а затем 30 - все еще отдельный диапазон. Я имею дело с большими объемами данных, поэтому я бы предпочел не использовать курсор для производительности (это то, что я делал раньше, и немного замедлял ситуацию)... Каков наилучший способ достичь этого? Я не SQL pro, поэтому я не уверен, есть ли доступная функция, которая может сделать это проще или что может быть самым быстрым способом для этого.

Спасибо за помощь.

Ответ 1

Главное наблюдение заключается в том, что последовательность чисел минус другая последовательность является константой. Мы можем сгенерировать другую последовательность, используя row_number. Это идентифицирует все группы:

select id, MIN(number) as low, MAX(number) as high
from (select t.*,
             (number - ROW_NUMBER() over (partition by id order by number) ) as groupnum
      from t
     ) t
group by id, groupnum

Остальное - это просто агрегация.

Ответ 2

Решение с CTE и рекурсией:

WITH CTE AS (
  SELECT T.ID, T.NUMBER, T.NUMBER AS GRP
  FROM T 
  LEFT OUTER JOIN T T2 ON T.ID = T2.ID AND T.NUMBER -1 = T2.NUMBER 
  WHERE T2.ID IS NULL
  UNION  ALL
  SELECT T.ID, T.NUMBER, GRP
  FROM CTE 
  INNER JOIN T
  ON T.ID = CTE.ID AND T.NUMBER  = CTE.NUMBER + 1
)
SELECT ID, MAX( NUMBER ), MIN(NUMBER)
FROM CTE
GROUP BY ID, GRP

Результаты на fiddlesql

Ответ 3

Я бы предложил использовать структуру цикла WHILE с табличной переменной вместо курсора.

Например,

DECLARE @TableVariable TABLE
(
    MyID int IDENTITY (1, 1) PRIMARY KEY NOT NULL,
    [ID] int,
    [Number] int
)

DECLARE @Count int, @Max int

INSERT INTO @TableVariable (ID, Number)
SELECT ID, Number
FROM YourSourceTable

SELECT @Count = 1, @Max = MAX(MyID)
FROM @TableVariable

WHILE @Count <= @Max
BEGIN

    ...do your processing here...


    SET @Count = @Count + 1

END