扩大用于在SQL中连接的整数范围

Question

我有一个表，在一个字段中存储整数范围，有点像打印范围，(例如"1-2,4-7,9-11")。此字段还可以包含单个数字。

我的目标是将这个表连接到第二个表，该表具有离散值而不是范围。

因此，如果表1包含

1-2,5
9-15
7

表二包含

1
2
3
4
5
6
7
8
9
10

连接的结果将是

1-2,5   1
1-2,5   2
1-2,5   5
7       7
9-15    9
9-15    10

在SQL Server2008 R2中工作。

Answer 1

使用string split function of your choice拆分逗号。计算出最小/最大值，并使用between进行连接。

SQL Fiddle

MS SQL Server 2012架构安装程序

create table T1(Col1 varchar(10))
create table T2(Col2 int)

insert into T1 values
('1-2,5'),
('9-15'),
('7')

insert into T2 values (1),(2),(3),(4),(5),(6),(7),(8),(9),(10)

查询1

select T1.Col1,
       T2.Col2
from T2
  inner join (
              select T1.Col1,
                     cast(left(S.Item, charindex('-', S.Item+'-')-1) as int) MinValue,
                     cast(stuff(S.Item, 1, charindex('-', S.Item), '') as int) MaxValue
              from T1
                cross apply dbo.Split(T1.Col1, ',') as S
             ) as T1
    on T2.Col2 between T1.MinValue and T1.MaxValue

|  COL1 | COL2 |
----------------
| 1-2,5 |    1 |
| 1-2,5 |    2 |
| 1-2,5 |    5 |
|  9-15 |    9 |
|  9-15 |   10 |
|     7 |    7 |

Answer 2

就像每个人都说的那样，在SQL Server中进行本机操作是一件痛苦的事情。如果你必须，那么我认为这是正确的方法。

首先确定解析字符串的规则，然后将过程分解为定义良好且易于理解的问题。

基于你的例子，我认为这是一个过程：

1. 将字符串中的逗号分隔值分隔成行
2. 如果数据不包含破折号，则它已结束(它是一个独立值)
3. 如果它不包含破折号E211包含破折号，请根据给定的左侧和右侧(范围)解析破折号H212的左侧和右侧
4. 确定它们之间的所有值成行

我将创建一个临时表来填充解析结果，其中需要两列：

SourceRowID INT, ContainedValue INT

另一个用于中间处理：

SourceRowID INT, ContainedValues VARCHAR

使用这样的CTE将逗号分隔值解析为各自的行步骤1现在是一个定义良好且易于理解的问题，需要解决

Turning a Comma Separated string into individual rows

所以你从源头上得到的结果

'1-2,5'

将是：

'1-2'

'5'

然后，从处理表中字段不包含破折号的地方进行SELECT。步骤2现在是一个定义良好且易于理解的问题，用于解决，这些是独立的数字，可以直接进入结果临时表。结果表还应该获得对原始行的ID引用。

下一步是使用CHARINDEX解析破折号左侧和右侧的值以定位它，然后根据需要使用适当的LEFT和RIGHT函数。这将为您提供起始值和结束值。

下面是实现此的相关问题步骤3现在是一个定义明确且易于理解的问题，需要解决

T-SQL substring - separating first and last name

现在，您已经分隔了起始值和结束值。使用另一个可以扩大这个范围的函数。步骤4现在是用于解决的定义明确且易于理解的问题

SQL: create sequential list of numbers from various starting points

SELECT all N between @min and @max

What is the best way to create and populate a numbers table?

并将其插入到临时表中。

现在，您应该有一个临时表，其中包含分解范围内的每个值。

现在只需将其JOIN到值上的另一个表，然后再到ID引用上的源表，就可以了。

Answer 3

我的建议是在您的范围表中添加更多字段和更多记录。具体来说，主键将是整数，另一个字段将是范围。记录将如下所示：

number    range
1         1-2,5
2         1-2,5
3         na
4         na
5         1-2,5

等

话虽如此，这仍然是相当有限的，因为一个数字只能有一个范围。如果你想彻底，在数字和范围之间建立一个多对多的关系。