如何在两个数字之间生成一个数字范围?

I have two numbers as input from the user, like for example 1000 and 1050.

我有两个数字作为用户的输入，比如1000和1050。

How do I generate the numbers between these two numbers, using a sql query, in seperate rows? I want this:

如何在分离行中使用sql查询生成这两个数字之间的数字?我想要这样的:

 1000
 1001
 1002
 1003
 .
 .
 1050

19 个解决方案

#1

You can select imaginary values, by using the VALUES keyword. Some JOINs will then generate lots and lots of combinations (can be extended to create hundreds of thousands of rows).

您可以通过使用values关键字来选择虚值。然后，一些连接将生成大量的组合(可以扩展为创建数十万行)。

Your numbers (from 1000 to 1050):

您的电话号码(1000 - 1050):

SELECT ones.n + 10*tens.n + 1000
FROM (VALUES(0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) ones(n),
     (VALUES(0),(1),(2),(3),(4),(5)                ) tens(n)
WHERE ones.n + 10*tens.n + 1000 BETWEEN 1000 AND 1050

Demo

演示

Pros:

优点:

• Lightning fast
• 闪电快
• No limits
• 没有限制
• Kind of readable
• 的可读
• Can be used inside of views
• 可以在视图内部使用吗

Cons:

缺点:

• very big numbers require lots of joins
• 非常大的数字需要很多连接

Another example, generating numbers from 0 to 9999:

另一个例子，生成从0到9999的数字:

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

Demo

演示

A shorter, but not as easy to read alternative:

一个更短，但不那么容易阅读的选择:

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

Demo

演示

#2

an alternative solution is recursive CTE:

另一种解决方案是递归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)

#3

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

Demo

演示

Note that this table has a maximum of 2048 because then the numbers have gaps.

请注意，该表的最大值是2048，因为这样数字就有差距。

Here's a slightly better approach using a system view(since from SQL-Server 2005):

这里有一个使用系统视图的稍微好一点的方法(从sql server 2005开始):

;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;

Demo

演示

or use a custom a number-table. Credits to Aaron Bertrand, i suggest to read the whole article: Generate a set or sequence without loops

或者使用定制的数字表。感谢Aaron Bertrand，我建议阅读全文:生成一个没有循环的集合或序列

#4

I recently wrote this inline table valued function to solve this very problem. It's not limited in range other than memory and storage. It accesses no tables so there's no need for disk reads or writes generally. It adds joins values exponentially on each iteration so it's very fast even for very large ranges. It creates ten million records in five seconds on my server. It also works with negative values.

我最近编写了这个内联表值函数来解决这个问题。它不受内存和存储范围的限制。它不访问任何表，因此不需要对磁盘进行读写操作。它在每次迭代中以指数形式添加值，因此即使在非常大的范围内，它也非常快。它在我的服务器上5秒内创建了1000万条记录。它也适用于负值。

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);

It's handy for date and time ranges as well:

它对日期和时间范围也很方便:

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'));

You could use a cross apply join on it to split records based on values in the table. So for example to create a record for every minute on a time range in a table you could do something like:

您可以使用交叉应用连接来根据表中的值分割记录。例如，要在一个表的时间范围内为每一分钟创建一个记录，你可以这样做:

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

#5

The best option I have used is as follows:

我使用的最佳选择如下:

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

I have generated millions of records using this and it works perfect.

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

#6

If you don't have a problem installing a CLR assembly in your server a good option is writing a table valued function in .NET. That way you can use a simple syntax, making it easy to join with other queries and as a bonus won't waste memory because the result is streamed.

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

Create a project containing the following class:

创建包含以下类的项目:

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() { }
    }
}

Put the assembly somewhere on the server and run:

将程序集放到服务器上，然后运行:

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];

Now you can run:

现在您可以运行:

select * from dbo.seq(1, 1000000)

#7

2 years later, but I found I had the same issue. Here is how I solved it. (edited to include parameters)

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

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)

#8

It work for me !

这对我有用!

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

#9

Here are couple quite optimal and compatible solutions:

以下是一些非常理想和兼容的解决方案:

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
;

#10

This will also do

这也会

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

#11

The best speed when run query

运行查询时的最佳速度

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

#12

recursive CTE in exponential size (even for default of 100 recursion, this can build up to 2^100 numbers):

递归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

#13

slartidan's answer can be improved, performance wise, by eliminating all references to the cartesian product and using ROW_NUMBER() instead (execution plan compared):

slartidan的答案可以通过消除对笛卡尔积的所有引用并使用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

Wrap it inside a CTE and add a where clause to select desired numbers:

将它封装在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

#14

I know I'm 4 years too late, but I stumbled upon yet another alternative answer to this problem. The issue for speed isn't just pre-filtering, but also preventing sorting. It's possible to force the join-order to execute in a manner that the Cartesian product actually counts up as a result of the join. Using slartidan's answer as a jump-off point:

我知道我晚了四年，但我偶然发现了另一个解决这个问题的方法。速度的问题不仅是预先过滤，而且还妨碍了排序。可以强制执行连接命令，而笛卡尔产品实际上是通过联接来计算的。用slartidan的答案作为出发点:

    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

If we know the range we want, we can specify it via @Upper and @Lower. By combining the join hint REMOTE along with TOP, we can calculate only the subset of values we want with nothing wasted.

如果我们知道我们想要的范围，我们可以通过@Upper和@Lower来指定它。通过将join提示远程与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

The join hint REMOTE forces the optimizer to compare on the right side of the join first. By specifying each join as REMOTE from most to least significant value, the join itself will count upwards by one correctly. No need to filter with a WHERE, or sort with an ORDER BY.

连接提示远程强制优化器首先在连接的右边进行比较。通过将每个连接指定为从大多数值到最不重要值的远程连接，连接本身将正确地向上计算一个。不需要用WHERE过滤，也不需要用ORDER BY排序。

If you want to increase the range, you can continue to add additional joins with progressively higher orders of magnitude, so long as they're ordered from most to least significant in the FROM clause.

如果您想要增加范围，您可以继续添加额外的连接，并逐步增加数量级，只要它们在from子句中从大多数到最不重要。

Note that this is a query specific to SQL Server 2008 or higher.

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

#15

I had to insert picture filepath into database using similar method. The query below worked fine:

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

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

The code for you would be:

您的代码是:

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

    SET @num = @num + 1
end

#16

-- 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

#17

This only works for sequences as long as some application table has rows. Assume I want sequence from 1..100, and have application table dbo.foo with column (of numeric or string type) foo.bar:

只要某些应用程序表有行，这就只适用于序列。假设我想要1的序列。100，并有应用表dbo。foo与列(数值或字符串类型)foo.bar:

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

Despite its presence in an order by clause, dbo.foo.bar does not have to have distinct or even non-null values.

尽管它存在于一个order by子句dbo.foo中。bar不需要具有不同的甚至非空值。

Of course, SQL Server 2012 has sequence objects, so there's a natural solution in that product.

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

#18

Here's what I came up with:

以下是我的想法:

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

Generates up to 2^24 values. Join conditions keep it fast for small values.

生成2 ^ 24值。对于小的值，连接条件使它保持快速。

#19

This completed for me in 36 seconds on our DEV server. Like Brian's answer, focusing on filtering to the range is important from within the query; a BETWEEN still tries to generate all the initial records prior to the lower bound even though it doesn't need them.

这在我们的DEV服务器上完成了36秒。就像Brian的回答一样，从查询内部过滤到范围是很重要的;间隔仍然尝试在下界之前生成所有的初始记录，即使它不需要它们。

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

Note that ROW_NUMBER is a bigint, so we can't go over 2^^64 (==16^^16) generated records with any method that uses it. This query therefore respects the same upper limit on generated values.

注意ROW_NUMBER是一个长整型数字,所以我们不能超过2 ^ ^ 64(= = 16 ^ ^ 16)生成的记录与任何使用它的方法。因此，该查询对生成的值具有相同的上限。