在没有Cursor的单个SQL表中合并数据

I have a table with an ID column and another column with a number. One ID can have multiple numbers. For example

我有一个带有ID列的表和另一个带有数字的列。一个ID可以有多个号码。例如

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

Now based of this data, in a new table, I want to have this

现在基于这些数据，在一个新表中，我希望有这个

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

As you can see, I want to merge any data where the numbers are consecutive, like 24, 25, 26. So now the low was 24, the high was 26, and then 30 is still a separate range. I am dealing with large amounts of data, so I would prefer to not use a cursor for performance sake (which is what I was previously doing, and was slowing things down quite a bit)...What is the best way to achieve this? I'm no SQL pro, so I'm not sure if there is a function available that could make this easier, or what the fastest way to accomplish this would be.

如你所见，我想合并任何数字连续的数据，如24,25,26。所以现在低点是24，高点是26，然后30仍然是一个单独的范围。我正在处理大量的数据，所以我宁愿不使用游标来提高性能（这是我以前做过的事情，而且速度慢了很多）...实现这个目标的最佳方法是什么？？我不是SQL专家，所以我不确定是否有可用的功能可以使这更容易，或者最快的方法是实现这一点。

Thanks for the help.

谢谢您的帮助。

4 个解决方案

#1

The key observation is that a sequence of numbers minus another sequence is a constant. We can generate another sequence using row_number. This identifies all the groups:

关键的观察是一系列数字减去另一个序列是一个常数。我们可以使用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

The rest is just aggregation.

其余的只是聚合。

#2

Solution with CTE and recursion:

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

Results at fiddlesql

结果在fiddlesql

#3

I'd suggest using a WHILE loop structure with a table variable instead of the cursor.

我建议使用带有表变量而不是光标的WHILE循环结构。

For example,

例如，

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

#4

CREATE TABLE Table1
    ([ID] int, [Number] int)
;

INSERT INTO Table1
    ([ID], [Number])
VALUES
    (1, 25),
    (1, 26),
    (1, 30),
    (1, 24),
    (2, 4),
    (2, 8),
    (2, 5)
;

    select ID, 
           MIN(Number)
         ,(SELECT MIN(Number) 
                  FROM (SELECT TOP 2 Number from Table1 WHERE ID =
                  T1.Id ORDER BY Number DESC) as DT)
    from Table1 as T1
    GROUP BY ID
    UNION 
    SELECT ID, MAX(Number), MAX(Number)
    FROM Table1 as T1
    GROUP BY ID;

Live Example

实例

#1