如何生成两个数字之间的数字范围？

小开

SELECT DISTINCT n = number
FROM master..[spt_values]
WHERE number BETWEEN @start AND @end

演示

请注意，此表的最大值为2048，因为这些数字之间存在间隙。

下面是使用系统视图的一个稍微好一点的方法（从SQL-Server2005开始）：

;WITH Nums AS
(
SELECT n = ROW_NUMBER() OVER (ORDER BY [object_id])
FROM sys.all_objects


)
SELECT n FROM Nums
WHERE n BETWEEN @start AND @end
ORDER BY n;

演示

或者使用自定义的数字表。感谢Aaron Bertrand，我建议阅读整篇文章：生成没有循环的集合或序列

小开

另一种解决方案是递归CTE：

DECLARE @startnum INT=1000
DECLARE @endnum INT=1050
;
WITH gen AS (
SELECT @startnum AS num
UNION ALL
SELECT num+1 FROM gen WHERE num+1<=@endnum
)
SELECT * FROM gen
option (maxrecursion 10000)

小开

以下是几个非常优化和兼容解决方案：

USE master;


declare @min as int;    set @min = 1000;
declare @max as int;    set @max = 1050;    --null returns all


--  Up to 256 - 2 048 rows depending on SQL Server version
select  isnull(@min,0)+number.number  as  number
FROM    dbo.spt_values  AS  number
WHERE   number."type"                   =   'P'     --integers
and (   @max                            is null     --return all
or  isnull(@min,0)+number.number    <=  @max    --return up to max
)
order by    number
;


--  Up to 65 536 - 4 194 303 rows depending on SQL Server version
select  isnull(@min,0)+value1.number+(value2.number*numberCount.numbers)  as  number
FROM  dbo.spt_values            AS  value1
cross join  dbo.spt_values    AS  value2
cross join (  --get the number of numbers (depends on version)
select  sum(1)  as  numbers
from    dbo.spt_values
where   spt_values."type"   =   'P' --integers
)                             as  numberCount
WHERE   value1."type" = 'P'   --integers
and value2."type" = 'P'   --integers
and (   @max    is null     --return all
or  isnull(@min,0)+value1.number+(value2.number*numberCount.numbers)
<=  @max            --return up to max
)
order by    number
;

小开

-- Generate Numeric Range
-- Source: http://www.sqlservercentral.com/scripts/Miscellaneous/30397/


CREATE TABLE #NumRange(
n int
)


DECLARE @MinNum int
DECLARE @MaxNum int
DECLARE @I int


SET NOCOUNT ON


SET @I = 0
WHILE @I <= 9 BEGIN
INSERT INTO #NumRange VALUES(@I)
SET @I = @I + 1
END




SET @MinNum = 1
SET @MaxNum = 1000000


SELECT  num = a.n +
(b.n * 10) +
(c.n * 100) +
(d.n * 1000) +
(e.n * 10000)
FROM    #NumRange a
CROSS JOIN #NumRange b
CROSS JOIN #NumRange c
CROSS JOIN #NumRange d
CROSS JOIN #NumRange e
WHERE   a.n +
(b.n * 10) +
(c.n * 100) +
(d.n * 1000) +
(e.n * 10000) BETWEEN @MinNum AND @MaxNum
ORDER BY a.n +
(b.n * 10) +
(c.n * 100) +
(d.n * 1000) +
(e.n * 10000)


DROP TABLE #NumRange

小开

这仅适用于某些应用程序表具有行的序列。假设我想要从1开始的序列。100，并使应用程序表dbo.foo具有列（数值或字符串类型）foo.bar：

select
top 100
row_number() over (order by dbo.foo.bar) as seq
from dbo.foo

尽管存在于ORDER BY子句中，但dbo.foo.bar不必具有不同的值，甚至不必具有非空值。

当然，SQL Server 2012具有序列对象，因此在该产品中有一个自然的解决方案。

小开

我最近编写了这个内联表值函数来解决这个问题。除了内存和存储之外，它不受范围限制。它不访问任何表，因此通常不需要磁盘读取或写入。它在每次迭代中以指数方式添加连接值，因此即使对于非常大的范围，它也非常快。它在五秒钟内在我的服务器上创建了1000，000条记录。它也适用于负值。

CREATE FUNCTION [dbo].[fn_ConsecutiveNumbers]
(
@start int,
@end  int
) RETURNS TABLE
RETURN


select
x268435456.X
| x16777216.X
| x1048576.X
| x65536.X
| x4096.X
| x256.X
| x16.X
| x1.X
+ @start
X
from
(VALUES (0),(1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11),(12),(13),(14),(15)) as x1(X)
join
(VALUES (0),(16),(32),(48),(64),(80),(96),(112),(128),(144),(160),(176),(192),(208),(224),(240)) as x16(X)
on x1.X <= @end-@start and x16.X <= @end-@start
join
(VALUES (0),(256),(512),(768),(1024),(1280),(1536),(1792),(2048),(2304),(2560),(2816),(3072),(3328),(3584),(3840)) as x256(X)
on x256.X <= @end-@start
join
(VALUES (0),(4096),(8192),(12288),(16384),(20480),(24576),(28672),(32768),(36864),(40960),(45056),(49152),(53248),(57344),(61440)) as x4096(X)
on x4096.X <= @end-@start
join
(VALUES (0),(65536),(131072),(196608),(262144),(327680),(393216),(458752),(524288),(589824),(655360),(720896),(786432),(851968),(917504),(983040)) as x65536(X)
on x65536.X <= @end-@start
join
(VALUES (0),(1048576),(2097152),(3145728),(4194304),(5242880),(6291456),(7340032),(8388608),(9437184),(10485760),(11534336),(12582912),(13631488),(14680064),(15728640)) as x1048576(X)
on x1048576.X <= @end-@start
join
(VALUES (0),(16777216),(33554432),(50331648),(67108864),(83886080),(100663296),(117440512),(134217728),(150994944),(167772160),(184549376),(201326592),(218103808),(234881024),(251658240)) as x16777216(X)
on x16777216.X <= @end-@start
join
(VALUES (0),(268435456),(536870912),(805306368),(1073741824),(1342177280),(1610612736),(1879048192)) as x268435456(X)
on x268435456.X <= @end-@start
WHERE @end >=
x268435456.X
| isnull(x16777216.X, 0)
| isnull(x1048576.X, 0)
| isnull(x65536.X, 0)
| isnull(x4096.X, 0)
| isnull(x256.X, 0)
| isnull(x16.X, 0)
| isnull(x1.X, 0)
+ @start


GO


SELECT X FROM fn_ConsecutiveNumbers(5, 500);

日期和时间范围也很方便：

SELECT DATEADD(day,X, 0) DayX
FROM fn_ConsecutiveNumbers(datediff(day,0,'5/8/2015'), datediff(day,0,'5/31/2015'))


SELECT DATEADD(hour,X, 0) HourX
FROM fn_ConsecutiveNumbers(datediff(hour,0,'5/8/2015'), datediff(hour,0,'5/8/2015 12:00 PM'));

您可以对其使用CROSS APPLY JOIN，以便根据表中的值拆分记录。例如，要在表中的时间范围内为每分钟创建一条记录，您可以执行以下操作：

select TimeRanges.StartTime,
TimeRanges.EndTime,
DATEADD(minute,X, 0) MinuteX
FROM TimeRanges
cross apply fn_ConsecutiveNumbers(datediff(hour,0,TimeRanges.StartTime),
datediff(hour,0,TimeRanges.EndTime)) ConsecutiveNumbers

小开

如果您在服务器中安装CLR程序集没有问题，一个好的选择是在.NET中编写一个表值函数。通过这种方式，您可以使用简单的语法，使其易于与其他查询连接，并且不会浪费内存，因为结果是流式的。

创建包含以下类的项目：

using System;
using System.Collections;
using System.Data;
using System.Data.Sql;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;


namespace YourNamespace
{
public sealed class SequenceGenerator
{
[SqlFunction(FillRowMethodName = "FillRow")]
public static IEnumerable Generate(SqlInt32 start, SqlInt32 end)
{
int _start = start.Value;
int _end = end.Value;
for (int i = _start; i <= _end; i++)
yield return i;
}


public static void FillRow(Object obj, out int i)
{
i = (int)obj;
}


private SequenceGenerator() { }
}
}

将程序集放在ABC_0_的某个位置，然后运行：

USE db;
CREATE ASSEMBLY SqlUtil FROM 'c:\path\to\assembly.dll'
WITH permission_set=Safe;


CREATE FUNCTION [Seq](@start int, @end int)
RETURNS TABLE(i int)
AS EXTERNAL NAME [SqlUtil].[YourNamespace.SequenceGenerator].[Generate];

现在，您可以运行：

select * from dbo.seq(1, 1000000)

小开

使用VALUES关键字选择非保留值。然后使用JOIN来生成大量的组合（可以扩展到创建数十万行甚至更多）。

简短快速的版本（不容易阅读）：

WITH x AS (SELECT n FROM (VALUES (0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) v(n))
SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
FROM x ones, x tens, x hundreds, x thousands
ORDER BY 1

演示

更详细的版本：

SELECT ones.n + 10*tens.n + 100*hundreds.n + 1000*thousands.n
FROM (VALUES(0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) ones(n),
(VALUES(0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) tens(n),
(VALUES(0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) hundreds(n),
(VALUES(0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) thousands(n)
ORDER BY 1

演示

这两个版本都可以轻松地使用WHERE子句进行扩展，将数字的输出限制在用户指定的范围内。如果你想重用它，你可以为它定义一个表值函数。

小开

我使用的最佳选择如下：

DECLARE @min bigint, @max bigint
SELECT @Min=919859000000 ,@Max=919859999999


SELECT TOP (@Max-@Min+1) @Min-1+row_number() over(order by t1.number) as N
FROM master..spt_values t1
CROSS JOIN master..spt_values t2

我已经用它生成了数百万条记录，它工作得很好。

小开

这也行。

DECLARE @startNum INT = 1000;
DECLARE @endNum INT = 1050;
INSERT  INTO dbo.Numbers
( Num
)
SELECT  CASE WHEN MAX(Num) IS NULL  THEN @startNum
ELSE MAX(Num) + 1
END AS Num
FROM    dbo.Numbers
GO 51

小开

运行查询时的最佳速度

DECLARE @num INT = 1000
WHILE(@num<1050)
begin
INSERT  INTO [dbo].[Codes]
(   Code
)
VALUES (@num)
SET @num = @num + 1
end

小开

我必须使用类似的方法将图片文件路径插入数据库。下面的查询运行良好：

DECLARE @num INT = 8270058
WHILE(@num<8270284)
begin
INSERT  INTO [dbo].[Galleries]
(ImagePath)
VALUES
('~/Content/Galeria/P'+CONVERT(varchar(10), @num)+'.JPG')


SET @num = @num + 1
end

你的代码应该是：

DECLARE @num INT = 1000
WHILE(@num<1051)
begin
SELECT @num


SET @num = @num + 1
end

小开

两年后，我发现我也有同样的问题。我是这样解决的。（编辑以包括参数）

DECLARE @Start INT, @End INT
SET @Start = 1000
SET @End = 1050


SELECT  TOP (@End - @Start+1) ROW_NUMBER() OVER (ORDER BY S.[object_id])+(@Start - 1) [Numbers]
FROM    sys.all_objects S WITH (NOLOCK)

小开

指数大小的递归CTE（即使是默认的100次递归，也可以累积到2^100个数字）：

DECLARE @startnum INT=1000
DECLARE @endnum INT=1050
DECLARE @size INT=@endnum-@startnum+1
;
WITH numrange (num) AS (
SELECT 1 AS num
UNION ALL
SELECT num*2 FROM numrange WHERE num*2<=@size
UNION ALL
SELECT num*2+1 FROM numrange WHERE num*2+1<=@size
)
SELECT num+@startnum-1 FROM numrange order by num

小开

这对我有用！

select top 50 ROW_NUMBER() over(order by a.name) + 1000 as Rcount
from sys.all_objects a

小开

通过消除对笛卡尔积的所有引用并使用ROW_NUMBER()来代替（执行计划比较），可以在性能方面改进司拉提丹的回答：

SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS n FROM
(VALUES(0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) x1(x),
(VALUES(0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) x2(x),
(VALUES(0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) x3(x),
(VALUES(0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) x4(x),
(VALUES(0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) x5(x)
ORDER BY n

将其包装在CTE中，并添加WHERE子句以选择所需的数字：

DECLARE @n1 AS INT = 100;
DECLARE @n2 AS INT = 40099;
WITH numbers AS (
SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS n FROM
(VALUES(0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) x1(x),
(VALUES(0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) x2(x),
(VALUES(0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) x3(x),
(VALUES(0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) x4(x),
(VALUES(0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) x5(x)
)
SELECT numbers.n
FROM numbers
WHERE n BETWEEN @n1 and @n2
ORDER BY n

小开

在我们的开发服务器上，我在36秒内完成了这项工作。与Brian的回答一样，从查询内部将重点放在过滤范围上是很重要的。Between仍然尝试生成下限之前的所有初始记录，即使它并不需要这些记录。

declare @s bigint = 10000000
,   @e bigint = 20000000


;WITH
Z AS (SELECT 0 z FROM (VALUES (0),(1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11),(12),(13),(14),(15)) T(n)),
Y AS (SELECT 0 z FROM Z a, Z b, Z c, Z d, Z e, Z f, Z g, Z h, Z i, Z j, Z k, Z l, Z m, Z n, Z o, Z p),
N AS (SELECT ROW_NUMBER() OVER (PARTITION BY 0 ORDER BY z) n FROM Y)


SELECT TOP (1+@e-@s) @s + n - 1 FROM N

请注意，行_编号是比金特，因此我们不能使用任何使用它的方法来检查2^^64（==16^^16）个生成的记录。因此，此查询对生成的值使用相同的上限。

小开

这是我想出来的：

create or alter function dbo.fn_range(@start int, @end int)  returns table
return
with u2(n) as (
select n
from (VALUES (0),(1),(2),(3)) v(n)
),
u8(n) as (
select
x0.n | x1.n * 4 | x2.n * 16 | x3.n * 64 as n
from u2 x0, u2 x1, u2 x2, u2 x3
)
select
@start + s.n as n
from (
select
x0.n | isnull(x1.n, 0) * 256 | isnull(x2.n, 0) * 65536 as n
from u8 x0
left join u8 x1 on @end-@start > 256
left join u8 x2 on @end-@start > 65536
) s
where s.n < @end - @start

最多生成2^24个值。对于较小的值，连接条件使其保持快速。

小开

我知道我晚了4年，但我偶然发现了这个问题的另一个答案。速度的问题不仅在于预过滤，还在于阻止排序。可以强制连接顺序以笛卡尔积作为连接结果实际计算的方式执行。以司拉提丹的回答为出发点：

    WITH x AS (SELECT n FROM (VALUES (0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) v(n))
SELECT ones.n + 10*tens.n + 100*hundreds.n + 1000*thousands.n
FROM x ones,     x tens,      x hundreds,       x thousands
ORDER BY 1

如果我们知道我们想要的范围，我们可以通过@upper和@lower指定它。通过将JOIN HINT REMOTE与TOP组合在一起，我们可以只计算我们想要的值的子集，而不会浪费任何东西。

WITH x AS (SELECT n FROM (VALUES (0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) v(n))
SELECT TOP (1+@Upper-@Lower) @Lower + ones.n + 10*tens.n + 100*hundreds.n + 1000*thousands.n
FROM x thousands
INNER REMOTE JOIN x hundreds on 1=1
INNER REMOTE JOIN x tens on 1=1
INNER REMOTE JOIN x ones on 1=1

远程连接提示强制优化器首先在连接的右侧进行比较。通过将每个连接指定为从最高有效值到最低有效值的远程连接，连接本身将正确地向上计数1。不需要使用WHERE进行筛选，也不需要使用ORDER BY进行排序。

如果要增加范围，可以继续添加更高数量级的其他连接，只要它们在FROM子句中按从最重要到最不重要的顺序排列即可。

请注意，这是特定于SQL Server 2008或更高版本的查询。

小开

它使用过程代码和表值函数。缓慢，但容易和可预测的。

CREATE FUNCTION [dbo].[Sequence] (@start int, @end int)
RETURNS
@Result TABLE(ID int)
AS
begin
declare @i int;
set @i = @start;
while @i <= @end
begin
insert into @result values (@i);
set @i = @i+1;
end
return;
end

用法：

SELECT * FROM dbo.Sequence (3,7);
ID
3
4
5
6
7

它是一个表，因此您可以在与其他数据的连接中使用它。我最常使用此函数作为按小时、天等对组进行连接的左侧，以确保时间值的连续序列。

SELECT DateAdd(hh,ID,'2018-06-20 00:00:00') as HoursInTheDay FROM dbo.Sequence (0,23) ;


HoursInTheDay
2018-06-20 00:00:00.000
2018-06-20 01:00:00.000
2018-06-20 02:00:00.000
2018-06-20 03:00:00.000
2018-06-20 04:00:00.000
(...)

性能并不令人兴奋（一百万行需要16秒），但对于许多用途来说已经足够好了。

SELECT count(1) FROM [dbo].[Sequence] (
1000001
,2000000)
GO

小开

我用递归CTE来做，但我不确定这是否是最好的方法。

declare @initial as int = 1000;
declare @final as int =1050;


with cte_n as (
select @initial as contador
union all
select contador+1 from cte_n
where contador <@final
) select * from cte_n option (maxrecursion 0)

萨卢多斯。

小开

甲骨文12c；快速但有限：

select rownum+1000 from all_objects fetch first 50 rows only;

注意：限制为所有_对象视图的行数；

小开

declare @start int = 1000
declare @end    int =1050


;with numcte
AS
(
SELECT @start [SEQUENCE]
UNION all
SELECT [SEQUENCE] + 1 FROM numcte WHERE [SEQUENCE] < @end
)
SELECT * FROM numcte

小开

没什么新鲜的，但我重写了Brian Pressler的解决方案，让眼睛看起来更容易，它可能对某些人有用（即使只是未来的我）：

alter function [dbo].[fn_GenerateNumbers]
(
@start int,
@end  int
) returns table
return


with
b0 as (select n from (values (0),(0x00000001),(0x00000002),(0x00000003),(0x00000004),(0x00000005),(0x00000006),(0x00000007),(0x00000008),(0x00000009),(0x0000000A),(0x0000000B),(0x0000000C),(0x0000000D),(0x0000000E),(0x0000000F)) as b0(n)),
b1 as (select n from (values (0),(0x00000010),(0x00000020),(0x00000030),(0x00000040),(0x00000050),(0x00000060),(0x00000070),(0x00000080),(0x00000090),(0x000000A0),(0x000000B0),(0x000000C0),(0x000000D0),(0x000000E0),(0x000000F0)) as b1(n)),
b2 as (select n from (values (0),(0x00000100),(0x00000200),(0x00000300),(0x00000400),(0x00000500),(0x00000600),(0x00000700),(0x00000800),(0x00000900),(0x00000A00),(0x00000B00),(0x00000C00),(0x00000D00),(0x00000E00),(0x00000F00)) as b2(n)),
b3 as (select n from (values (0),(0x00001000),(0x00002000),(0x00003000),(0x00004000),(0x00005000),(0x00006000),(0x00007000),(0x00008000),(0x00009000),(0x0000A000),(0x0000B000),(0x0000C000),(0x0000D000),(0x0000E000),(0x0000F000)) as b3(n)),
b4 as (select n from (values (0),(0x00010000),(0x00020000),(0x00030000),(0x00040000),(0x00050000),(0x00060000),(0x00070000),(0x00080000),(0x00090000),(0x000A0000),(0x000B0000),(0x000C0000),(0x000D0000),(0x000E0000),(0x000F0000)) as b4(n)),
b5 as (select n from (values (0),(0x00100000),(0x00200000),(0x00300000),(0x00400000),(0x00500000),(0x00600000),(0x00700000),(0x00800000),(0x00900000),(0x00A00000),(0x00B00000),(0x00C00000),(0x00D00000),(0x00E00000),(0x00F00000)) as b5(n)),
b6 as (select n from (values (0),(0x01000000),(0x02000000),(0x03000000),(0x04000000),(0x05000000),(0x06000000),(0x07000000),(0x08000000),(0x09000000),(0x0A000000),(0x0B000000),(0x0C000000),(0x0D000000),(0x0E000000),(0x0F000000)) as b6(n)),
b7 as (select n from (values (0),(0x10000000),(0x20000000),(0x30000000),(0x40000000),(0x50000000),(0x60000000),(0x70000000)) as b7(n))


select s.n
from (
select
b7.n
| b6.n
| b5.n
| b4.n
| b3.n
| b2.n
| b1.n
| b0.n
+ @start
n
from b0
join b1 on b0.n <= @end-@start and b1.n <= @end-@start
join b2 on b2.n <= @end-@start
join b3 on b3.n <= @end-@start
join b4 on b4.n <= @end-@start
join b5 on b5.n <= @end-@start
join b6 on b6.n <= @end-@start
join b7 on b7.n <= @end-@start
) s
where @end >= s.n


GO

小开

这就是我所做的，它非常快速和灵活，而且没有太多的代码。

DECLARE @count  int =   65536;
DECLARE @start  int =   11;
DECLARE @xml    xml =   REPLICATE(CAST('<x/>' AS nvarchar(max)), @count);


; WITH GenerateNumbers(Num) AS
(
    SELECT  ROW_NUMBER() OVER (ORDER BY @count) + @start - 1
    FROM    @xml.nodes('/x') X(T)
)
SELECT  Num
FROM    GenerateNumbers;

请注意，（ORDER BY@COUNT）是一个哑元。它不执行任何操作，但行_number（）需要ORDER BY.

编辑：我意识到最初的问题是得到一个从X到y的范围。我的脚本可以像这样修改以获得一个范围：

DECLARE @start  int =   5;
DECLARE @end    int =   21;
DECLARE @xml    xml =   REPLICATE(CAST('<x/>' AS nvarchar(max)), @end - @start + 1);


; WITH GenerateNumbers(Num) AS
(
    SELECT  ROW_NUMBER() OVER (ORDER BY @end) + @start - 1
    FROM    @xml.nodes('/x') X(T)
)
SELECT  Num
FROM    GenerateNumbers;

小开

我已经开发并使用了相当长一段时间的解决方案（利用其他人的共享作品）与至少一个发布的解决方案略有相似。它不引用任何表，并返回最多1048576个值（2^20）的未排序范围，如果需要，还可以包含负数。当然，如有必要，您可以对结果进行排序。它的运行速度相当快，尤其是在较小的范围内。

Select value from dbo.intRange(-500, 1500) order by value  -- returns 2001 values


create function dbo.intRange
(
@Starting as int,
@Ending as int
)
returns table
as
return (
select value
from (
select @Starting +
( bit00.v | bit01.v | bit02.v | bit03.v
| bit04.v | bit05.v | bit06.v | bit07.v
| bit08.v | bit09.v | bit10.v | bit11.v
| bit12.v | bit13.v | bit14.v | bit15.v
| bit16.v | bit17.v | bit18.v | bit19.v
) as value
from       (select 0 as v union ALL select 0x00001 as v) as bit00
cross join (select 0 as v union ALL select 0x00002 as v) as bit01
cross join (select 0 as v union ALL select 0x00004 as v) as bit02
cross join (select 0 as v union ALL select 0x00008 as v) as bit03
cross join (select 0 as v union ALL select 0x00010 as v) as bit04
cross join (select 0 as v union ALL select 0x00020 as v) as bit05
cross join (select 0 as v union ALL select 0x00040 as v) as bit06
cross join (select 0 as v union ALL select 0x00080 as v) as bit07
cross join (select 0 as v union ALL select 0x00100 as v) as bit08
cross join (select 0 as v union ALL select 0x00200 as v) as bit09
cross join (select 0 as v union ALL select 0x00400 as v) as bit10
cross join (select 0 as v union ALL select 0x00800 as v) as bit11
cross join (select 0 as v union ALL select 0x01000 as v) as bit12
cross join (select 0 as v union ALL select 0x02000 as v) as bit13
cross join (select 0 as v union ALL select 0x04000 as v) as bit14
cross join (select 0 as v union ALL select 0x08000 as v) as bit15
cross join (select 0 as v union ALL select 0x10000 as v) as bit16
cross join (select 0 as v union ALL select 0x20000 as v) as bit17
cross join (select 0 as v union ALL select 0x40000 as v) as bit18
cross join (select 0 as v union ALL select 0x80000 as v) as bit19
) intList
where @Ending - @Starting < 0x100000
and intList.value between @Starting and @Ending
)

小开

;WITH u AS (
SELECT Unit FROM (VALUES (0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) v(Unit)
),
d AS (
SELECT
(Thousands+Hundreds+Tens+Units) V
FROM
(SELECT Thousands = Unit * 1000 FROM u) Thousands
,(SELECT Hundreds = Unit * 100 FROM u) Hundreds
,(SELECT Tens = Unit * 10 FROM u) Tens
,(SELECT Units = Unit FROM u) Units
WHERE
(Thousands+Hundreds+Tens+Units) <= 10000
)


SELECT * FROM d ORDER BY v

小开

读完这个帖子后，我做了下面的功能。简单快捷：

go
create function numbers(@begin int, @len int)
returns table as return
with d as (
select 1 v from (values(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) d(v)
)
select top (@len) @begin -1 + row_number() over(order by (select null)) v
from d d0
cross join d d1
cross join d d2
cross join d d3
cross join d d4
cross join d d5
cross join d d6
cross join d d7
go


select * from numbers(987654321,500000)

小开

SQL 2017及更高版本的

更新：如果你想要的序列是<；8K，那么这将工作：

Declare @start_num int = 1000
,   @end_num int = 1050


Select [number] = @start_num + ROW_NUMBER() over (order by (Select null))
from string_split(replicate(' ',@end_num-@start_num-1),' ')

小开

下面是一个通用且相对快速的解决方案，它输出从1到@n的整数。它适用于@n的任何正整数（非常大的数字将导致算术溢出），而无需添加或删除表连接。它不需要使用系统表，也不需要更改最大递归。

declare @n int = 10000


;with d as (select * from (values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) x (d)),
n as (
select d x from d where d > 0 and d <= @n
union all
select x * 10 + d from n, d where x * 10 + d <= @n
)
select x from n

您可以添加order by子句来对数字进行排序。

小开

DECLARE @a int=1000, @b int=1050
SELECT @a-1+ROW_NUMBER() OVER(ORDER BY y.z.value('(/n)[1]', 'int') ) rw
FROM (
SELECT CAST('<m>'+REPLICATE('<n>1</n>', @b-@a+1)+'</m>' AS XML ) x ) t
CROSS APPLY t.x.nodes('//m/n') y(z)

小开

CREATE OR ALTER  FUNCTION [dbo].[_ICAN_TF_Nums2](@a INT, @b INT)
-------------------------------------------------------------------------------------------------------------------
--INVENTIVE:Keyvan ARYAEE-MOEEN
-------------------------------------------------------------------------------------------------------------------
RETURNS @_ICAN_TF_Nums2 TABLE
(
num int
)
AS
BEGIN
------------------------------------------------------------------------------------------------------------------
WITH nums AS
(SELECT @a AS value
UNION ALL
SELECT value + 1 AS value
FROM nums
WHERE nums.value < @b)
INSERT @_ICAN_TF_Nums2
SELECT *
FROM nums
ORDER BY 1
option ( MaxRecursion 0 );
RETURN
END
-------------------------------------------------------------------------------------------------------------------
-- SELECT * FROM dbo._ICAN_TF_Nums2(1000, 1050)
-------------------------------------------------------------------------------------------------------------------

小开

更简单的解决方案，采用https://www.postgresql.org/docs/9.1/queries-with.html

WITH RECURSIVE t(n) AS (
SELECT 1
UNION ALL
SELECT n+1 FROM t
)
SELECT n FROM t LIMIT 100;

小开

如果您想使用递归CTE来生成范围而不遇到递归限制，您可以使用类似下面的内容（MySQL方言，但应该不难转换为SQL Server）：

WITH RECURSIVE
a (x) AS
(SELECT 0 UNION ALL SELECT 1),
b (x) AS
(SELECT x FROM a
UNION ALL
SELECT (b.x + 1) * 2 + a.x FROM a, b WHERE (b.x + 1) * 2 + a.x < 10000)
SELECT x FROM b
ORDER BY x;

递归的每一步产生的结果是前一步的两倍，因此递归的总数约为log2（n）而不是n.

如果要确保生成的数字是有序的，则需要ORDER BY子句，否则可以安全地省略该子句。

如果您想要一个从零以外的数字开始的范围，只需向最终结果添加一个偏移量，例如SELECT x + offset FROM b

小开

虽然关于这个话题有很多好的答案，但在我看来，较新的TSQL（2019+）语法为这个问题提供了更简单、更容易理解的解决方案：

Declare @n1 int = 1000
Declare @n2 int = 1050
Declare @n  int = @n2 -@n1
SELECT @n1 -1 + Row_Number() over ( partition by 1 order by value) as Val FROM
STRING_SPLIT ( (SELECT REPLICATE(';', @n)) , ';' )

小开

在新版本的MSSQL Server中，存在以下序列：https://learn.microsoft.com/en-us/sql/relational-databases/sequence-numbers/sequence-numbers?view=sql-server-ver15

示例：

CREATE SEQUENCE SequenceFrom1000
AS tinyint
START WITH 1000
INCREMENT BY 1
MINVALUE 1000
MAXVALUE 1050
GO

像这样使用

SELECT YourTable.Column1, YourTable.Column2, NEXT VALUE FOR SequenceFrom1000 AS GeneratedNumber,FROM YourTable;
GO

小开

如果您的SQL-Server版本高于2022或支持生成_系列函数，我们可以尝试使用生成_系列函数，并声明START和STOP参数。

GENERATE_SERIES返回包含一系列值的单列表格，其中每个值都与前面的BY步骤不同

SELECT [Value]
FROM GENERATE_SERIES(START = 1000, STOP = 1050)

小开

一个在SQLite中工作（这给出了0到999）

with Ns as (select n FROM ( select 0 n union select 1 n union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9)),
srl as ( SELECT  a.n hundreds, b.n tens, c.n ones ,a.n*100+b.n*10+c.n serial from  Ns a, Ns b , Ns c)
select * from srl

小开

根据有关SQL Server 2022新功能的在线文档，生成_系列（）功能将提供另一个选项：

SELECT [value]
FROM GENERATE_SERIES(START = 1000, STOP = 1500, STEP = 1);

小开

CREATE OR ALTER FUNCTION [dbo].[RangeGenerator](
@Start  BIGINT
,   @Stop   BIGINT
,   @Step   BIGINT
)
RETURNS TABLE AS RETURN (


WITH [Initial] AS (
SELECT
[n]
FROM (VALUES
(0), (0), (0), (0), (0), (0), (0), (0), (0), (0)
,   (0), (0), (0), (0), (0), (0), (0), (0), (0), (0)
,   (0), (0), (0), (0), (0), (0), (0), (0), (0), (0)
,   (0), (0), (0), (0), (0), (0), (0), (0), (0), (0)
,   (0), (0), (0), (0), (0), (0), (0), (0), (0), (0)
,   (0), (0), (0), (0), (0), (0), (0), (0), (0), (0)
,   (0), (0), (0), (0), (0), (0), (0), (0), (0), (0)
,   (0), (0), (0), (0), (0), (0), (0), (0), (0), (0)
,   (0), (0), (0), (0), (0), (0), (0), (0), (0), (0)
,   (0), (0), (0), (0), (0), (0), (0), (0), (0), (0)
) AS [t]([n])
)
SELECT TOP ((@Stop - @Start) / @Step + 1)
[n] = @Start + ROW_NUMBER() OVER (ORDER BY [i0].[n]) * @Step - @Step
FROM        [Initial]   AS  [i0] /*       100 */
CROSS JOIN  [Initial]   AS  [i1] /*     10000 */
CROSS JOIN  [Initial]   AS  [i2] /*   1000000 */
ORDER BY 1


);
GO


SELECT [n] FROM [dbo].[RangeGenerator](0, 100, 2);