Como gerar um intervalo de números entre dois números?

Eu tenho dois números como input do usuário, como por exemplo 1000 e 1050 .

Como faço para gerar os números entre esses dois números, usando uma consulta sql, em linhas separadas? Eu quero isso:

  1000 1001 1002 1003 . . 1050 

Você pode selecionar valores imaginários usando a palavra-chave VALUES . Alguns JOIN s irão gerar muitas e muitas combinações (podem ser estendidas para criar centenas de milhares de linhas).

 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 

Demonstração

Para o seu caso de uso específico, ele pode ser encurtado (porque você precisa apenas de números de 1000 a 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 ORDER BY 1 

Demonstração

Uma alternativa mais curta, mas não tão fácil de ler:

 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 

Demonstração

uma solução alternativa é o CTE recursivo:

 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) 
 SELECT DISTINCT n = number FROM master..[spt_values] WHERE number BETWEEN @start AND @end 

Demonstração

Note que esta tabela tem um máximo de 2048, porque os números têm lacunas.

Aqui está uma abordagem um pouco melhor usando uma visualização do sistema (desde o 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; 

Demonstração

ou use uma tabela numérica personalizada. Créditos para Aaron Bertrand, eu sugiro ler todo o artigo: Gerar um conjunto ou seqüência sem loops

Eu escrevi recentemente esta function de valor de tabela inline para resolver este problema muito. Não é limitado em alcance diferente de memory e armazenamento. Não acessa nenhuma tabela, portanto não há necessidade de leituras ou gravações de disco em geral. Ele adiciona valores de junções exponencialmente em cada iteração, então é muito rápido mesmo para intervalos muito grandes. Cria dez milhões de registros em cinco segundos no meu servidor. Também funciona com valores negativos.

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

É útil também para intervalos de data e hora:

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

Você poderia usar uma união cruzada para dividir registros com base nos valores da tabela. Por exemplo, para criar um registro para cada minuto em um intervalo de tempo em uma tabela, você poderia fazer algo como:

 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 

A melhor opção que usei é a seguinte:

 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 

Gerei milhões de registros usando isso e funciona perfeitamente.

Se você não tiver problemas para instalar um assembly CLR em seu servidor, uma boa opção é escrever uma function com valor de tabela no .NET. Dessa forma, você pode usar uma syntax simples, facilitando a junit com outras consultas e, como bônus, não desperdiçará memory, pois o resultado é transmitido.

Crie um projeto contendo a seguinte 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() { } } } 

Coloque a assembly em algum lugar no servidor e execute:

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

Agora você pode executar:

 select * from dbo.seq(1, 1000000) 

2 anos depois, mas descobri que tinha o mesmo problema. Aqui está como eu resolvi isso. (editado para include parâmetros)

 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) 

Funciona para mim!

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

Aqui estão algumas soluções ótimas e compatíveis:

 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 ; 

Isso também fará

 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 

A melhor velocidade quando executar consulta

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

CTE recursivo em tamanho exponencial (mesmo para o padrão de 100 recursion, isso pode gerar até 2 ^ 100 números):

 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 

A resposta de slartidan pode ser melhorada, melhorando o desempenho, eliminando todas as referências ao produto cartesiano e usando ROW_NUMBER() vez disso ( plano de execução comparado ):

 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 

Envolva-o dentro de um CTE e adicione uma cláusula where para selecionar os números desejados:

 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 

Eu sei que estou 4 anos atrasado, mas me deparei com outra resposta alternativa para esse problema. O problema da velocidade não é apenas a pré-filtragem, mas também a prevenção da sorting. É possível forçar a ordem de junit a ser executada de uma maneira que o produto cartesiano realmente conta como resultado da junit. Usando a resposta de slartidan como um ponto de partida:

  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 

Se soubermos o intervalo que queremos, podemos especificá-lo via @Upper e @Lower. Combinando a dica de união REMOTE junto com TOP, podemos calcular apenas o subconjunto de valores que queremos sem nada desperdiçado.

 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 

A dica de união REMOTE força o otimizador a comparar no lado direito da junit primeiro. Especificando cada junit como REMOTE do valor mais significativo para o menos significativo, a própria junit contará para cima por um corretamente. Não há necessidade de filtrar com um WHERE ou classificar com um ORDER BY.

Se você quiser aumentar o intervalo, pode continuar adicionando junções adicionais com ordens de magnitude progressivamente maiores, desde que sejam ordenadas de mais para menos significativas na cláusula FROM.

Observe que esta é uma consulta específica para o SQL Server 2008 ou superior.

A melhor maneira é usar códigos recursivos.

 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) 

saludos.

Eu tive que inserir caminho de imagem no database usando o método similar. A consulta abaixo funcionou bem:

 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 

O código para você seria:

 DECLARE @num INT = 1000 WHILE(@num<1051) begin SELECT @num SET @num = @num + 1 end 
 -- 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 = an + (bn * 10) + (cn * 100) + (dn * 1000) + (en * 10000) FROM #NumRange a CROSS JOIN #NumRange b CROSS JOIN #NumRange c CROSS JOIN #NumRange d CROSS JOIN #NumRange e WHERE an + (bn * 10) + (cn * 100) + (dn * 1000) + (en * 10000) BETWEEN @MinNum AND @MaxNum ORDER BY an + (bn * 10) + (cn * 100) + (dn * 1000) + (en * 10000) DROP TABLE #NumRange 

Isso só funciona para seqüências, desde que algumas tabelas de aplicativos tenham linhas. Suponha que eu quero seqüência de 1..100, e tem tabela de aplicativos dbo.foo com coluna (de tipo numérico ou string) foo.bar:

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

Apesar de sua presença em uma ordem por cláusula, dbo.foo.bar não precisa ter valores distintos ou até mesmo nulos.

Obviamente, o SQL Server 2012 possui objects de sequência, portanto, há uma solução natural nesse produto.

Aqui está o que eu criei:

 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 + sn 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 sn < @end - @start 

Gera até 2 ^ 24 valores. As condições de junit mantêm a velocidade para valores pequenos.

Isto completou para mim em 36 segundos no nosso servidor DEV. Como a resposta de Brian, concentrar-se na filtragem para o intervalo é importante de dentro da consulta; a ENTRE ainda tenta gerar todos os registros iniciais antes do limite inferior, mesmo que não precise deles.

 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 que ROW_NUMBER é um bigint , então não podemos ultrapassar 2 ^^ 64 (== 16 ^^ 16) registros gerados com qualquer método que o use. Essa consulta, portanto, respeita o mesmo limite superior nos valores gerados.

Isso usa código procedural e uma function com valor de tabela. Lento, mas fácil e previsível.

 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 

Uso:

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

É uma tabela, então você pode usá-la em junções com outros dados. Com mais freqüência, uso essa function como o lado esquerdo de uma junit em uma hora, dia, GROUP BY, etc. para garantir uma sequência contígua de valores de tempo.

 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 (...) 

O desempenho é pouco inspirador (16 segundos para um milhão de linhas), mas bom o suficiente para muitos propósitos.

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