sql - 生成范围之间的数字

Question

假设我有 2-6 并且通过使用下面的程序我能够生成范围

DECLARE @range VARCHAR(10) = '2-6'
DECLARE @startRange INT = PARSENAME(REPLACE(@range, '-', '.'), 2)
DECLARE @lastRange INT = PARSENAME(REPLACE(@range, '-', '.'), 1)

SELECT DISTINCT Number = number
FROM master..spt_values
WHERE number BETWEEN @startRange AND @lastRange

现在我的输入为2-6,9-12,15-20

我知道通过使用 , 作为分隔符分割记录并使用上述查询作为函数，我可以实现目标。但是，我想使用 CROSS APPLY 来避免函数。

我已经尝试如下

DECLARE @range VARCHAR(50) = '2-6,9-12,15-20'

SELECT
    value   
FROM STRING_SPLIT(@range,',')

但是如何将 CROSS APPLY 与上述查询与 master..spt_values 结合使用？

Answer 1

如果您只是提取每个项目的开始和结束位置，您可以使用以下内容:

DECLARE @Value VARCHAR(20) = '2-6';

SELECT  Start = TRY_CONVERT(INT, LEFT(@Value, CHARINDEX('-', @Value) - 1)),
        [End] = TRY_CONVERT(INT, SUBSTRING(@Value, CHARINDEX('-', @Value) + 1, LEN(@Value)));

这给出:

Start   End
------------
2       6

然后您只需加入master..spt_values即可:

DECLARE @range VARCHAR(50) = '2-6,9-12,15-20'

SELECT  s.Value, v.Number
FROM    STRING_SPLIT(@range,',') AS s
        INNER JOIN master..spt_values AS v
            ON v.Number >= TRY_CONVERT(INT, LEFT(s.Value, CHARINDEX('-', s.Value) - 1))
            AND v.Number <= TRY_CONVERT(INT, SUBSTRING(s.Value, CHARINDEX('-', s.Value) + 1, LEN(s.Value)))
            AND v.Type = 'P';

对于master..spt_values的值(value)来说，它是相当有限的，有更好的方法generate a set or sequence without a loop .

当使用master..spt_values时，范围2500-2501将不起作用，但如果您有一个数字表或不同的数字源，那么它会是很好，例如以下内容最多可达 10,000，然后通过取消注释其他行，您可以增加范围:

DECLARE @range VARCHAR(50) = '2-6,9-12,15-20,2500-2501';

WITH Numbers (Number) AS
(   SELECT  ROW_NUMBER() OVER(ORDER BY N1.N)
    FROM (VALUES (1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) AS N1 (N)         -- 100
    CROSS JOIN (VALUES (1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) AS N2 (N)   -- 100
    CROSS JOIN (VALUES (1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) AS N3 (N)   -- 1,000
    CROSS JOIN (VALUES (1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) AS N4 (N)   -- 10,000
    --CROSS JOIN (VALUES (1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) AS N5 (N) -- 100,000
)
SELECT  s.Value, n.Number
FROM    STRING_SPLIT(@range,',') AS s
        INNER JOIN Numbers AS n
            ON n.Number >= TRY_CONVERT(INT, LEFT(s.Value, CHARINDEX('-', s.Value) - 1))
            AND n.Number <= TRY_CONVERT(INT, SUBSTRING(s.Value, CHARINDEX('-', s.Value) + 1, LEN(s.Value)))
ORDER BY n.Number;