在一组数字中查找连续范围 [英] Finding continuous ranges in a set of numbers

问题描述

我在数据库表中有相当大的一组电话号码(大约200万个).这些数字已插入到块中，因此存在许多连续的数字范围，范围从10个数字到1万个数字.其中一些号码正在使用中，因此标记为不可用，其余号码可用.给定一个特定的数字，我需要一种方法来查找该数字之上和之下的连续数字范围.该范围应一直持续到找到一个不可用的数字或遇到两个范围的边界为止.

I have a reasonably large set of phone numbers (approximately 2 million) in a database table. These numbers have been inserted in blocks, so there are many continuous ranges of numbers, anything from 10 numbers to 10 thousand in a range. Some of these numbers are in use and are therefore marked as unavailable, the rest are available. Given a particular number I need a way to find continuous ranges of numbers, both above and below that number. The range should continue until it finds an unavailable number, or encounters the boundary of two ranges.

例如，给定以下设置:

1000
1001
1002
1010
1011
1012
1013
1020
1021
1022

使用1012作为参数进行搜索应返回1010、1011、1012、1013.

Doing a search using 1012 as the parameter should return 1010, 1011, 1012, 1013.

形成查询以查找这些范围的好方法是什么?我们在SQL Server之上使用NHibernate，使用这两种方法都可以.

What is a good way of forming a query to find these ranges? We use NHibernate on top of SQL server, a solution using either is fine.

推荐答案

理论上，集合中的项目没有特定的值，因此我假设您还具有一些连续的ID列，这些列定义了数字的顺序.像这样:

Theoretically the items in a set have no particular value, so I'm assuming you also have some continuous ID column that defines the order of the numbers. Something like this:

ID  Number
1   1000
2   1001
3   1002
4   1010
5   1011
6   1012
7   1013
8   1020
9   1021
10  1022

您可以创建一个额外的列，其中包含Number - ID的结果:

You could create an extra column that contains the result of Number - ID:

ID  Number  Diff
1   1000    999
2   1001    999
3   1002    999
4   1010    1006
5   1011    1006
6   1012    1006
7   1013    1006
8   1020    1012
9   1021    1012
10  1022    1012

在相同范围内的数字将在差异"列中具有相同的结果.

Numbers in the same range will have the same result in the Diff column.

这篇关于在一组数字中查找连续范围的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！