在单个SQL表中合并数据而不使用Cursor [英] Merging data in a single SQL table without a Cursor

问题描述

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

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

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

如你所见，我想合并数字连续的任何数据， 25，26.所以现在的低点是24，高点是26，然后30仍然是一个单独的范围。我处理大量的数据，所以我宁愿不使用游标的性能的缘故（这是我以前做的，并放慢了一些东西）。什么是最好的方式来实现？我没有SQL pro，所以我不知道是否有一个可用的功能，可以使这更容易，或什么是最快的方式来完成这将是。

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.

感谢您的帮助。

推荐答案

一个数字序列减去另一个序列是一个常数。我们可以使用 row_number 生成另一个序列。这标识所有组：

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:

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

其余只是聚合。

这篇关于在单个SQL表中合并数据而不使用Cursor的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！