¿Cómo generar un rango de números entre dos números?

Tengo dos números como entrada del usuario, como por ejemplo 1000 y 1050 .

¿Cómo puedo generar los números entre estos dos números, utilizando una consulta sql, en filas separadas? Quiero esto:

  1000 1001 1002 1003 . . 1050 

Puede seleccionar valores imaginarios, utilizando la palabra clave VALUES . Algunos JOIN s generarán muchas y muchas combinaciones (se pueden ampliar para crear cientos de miles de filas).

 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 

Manifestación

Para su caso de uso específico, incluso puede acortarse (porque solo necesita números del 1000 al 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 

Manifestación

Una alternativa más corta, pero no tan fácil de leer:

 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 

Manifestación

una solución alternativa es 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 

Manifestación

Tenga en cuenta que esta tabla tiene un máximo de 2048 porque los números tienen espacios.

Aquí hay un enfoque un poco mejor usando una vista de sistema (desde 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; 

Manifestación

o use una tabla numérica personalizada. Créditos a Aaron Bertrand, sugiero leer todo el artículo: Generar un conjunto o secuencia sin bucles

Hace poco escribí esta función valorada en la tabla en línea para resolver este problema. No está limitado en el rango que no sea la memoria y el almacenamiento. No tiene acceso a tablas, por lo que no es necesario escribir o leer discos en general. Agrega valores de unión exponencial en cada iteración, por lo que es muy rápido incluso para rangos muy grandes. Crea diez millones de registros en cinco segundos en mi servidor. También funciona con 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); 

También es útil para los rangos de fecha y 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')); 

Puede usar una combinación de aplicación cruzada para dividir registros según los valores de la tabla. Entonces, por ejemplo, para crear un registro por cada minuto en un rango de tiempo en una tabla, podría hacer 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 

La mejor opción que he usado es la siguiente:

 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 

He generado millones de registros usando esto y funciona perfecto.

Si no tiene problemas para instalar un ensamblado CLR en su servidor, una buena opción es escribir una función con valores de tabla en .NET. De esta forma puede usar una syntax simple, lo que facilita la unión con otras consultas y, como beneficio adicional, no desperdiciará la memoria porque se transmitirá el resultado.

Crea un proyecto que contenga la siguiente clase:

 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 el ensamblaje en algún lugar del servidor y ejecute:

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

Ahora puedes ejecutar:

 select * from dbo.seq(1, 1000000) 

2 años después, pero descubrí que tenía el mismo problema. Así es como lo resolví. (editado para incluir 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 mí!

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

Aquí hay un par de soluciones bastante óptimas y compatibles:

 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 ; 

Esto también hará

 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 

La mejor velocidad cuando se ejecuta la consulta

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

CTE recursivo en tamaño exponencial (incluso para la recursión predeterminada de 100, esto puede generar hasta 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 

La respuesta de slartidan puede mejorarse, en cuanto al rendimiento, eliminando todas las referencias al producto cartesiano y utilizando ROW_NUMBER() lugar (se compara el plan de ejecución ):

 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 

Envuélvalo dentro de un CTE y agregue una cláusula Where para seleccionar los números deseados:

 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 

Sé que llegué 4 años tarde, pero me encontré con otra respuesta alternativa a este problema. El problema de la velocidad no es solo el filtrado previo, sino también la prevención de la clasificación. Es posible obligar a la orden de unión a que se ejecute de manera que el producto cartesiano realmente cuente como resultado de la unión. Usando la respuesta de Slartidan como punto 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 

Si conocemos el rango que queremos, podemos especificarlo a través de @Upper y @Lower. Al combinar la sugerencia de combinación REMOTE junto con TOP, podemos calcular solo el subconjunto de valores que queremos sin perder nada.

 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 

La sugerencia de unión REMOTE obliga al optimizador a comparar primero en el lado derecho de la unión. Al especificar cada combinación como REMOTO de mayor a menor valor significativo, la unión contará hacia arriba por uno correctamente. No es necesario filtrar con un WHERE u ordenar con un ORDER BY.

Si desea boost el rango, puede continuar agregando combinaciones adicionales con órdenes de magnitud progresivamente superiores, siempre que estén ordenadas de mayor a menor importancia en la cláusula FROM.

Tenga en cuenta que esta es una consulta específica para SQL Server 2008 o superior.

La mejor forma es usar ctes 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.

Tuve que insertar una ruta de archivo de imagen en la base de datos usando un método similar. La siguiente consulta funcionó bien:

 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 

El código para ti sería:

 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 

Esto solo funciona para secuencias siempre que alguna tabla de aplicación tenga filas. Supongamos que quiero secuencia de 1..100, y tengo la tabla de aplicación dbo.foo con columna (de tipo numérico o de cadena) foo.bar:

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

A pesar de su presencia en una cláusula order by, dbo.foo.bar no tiene que tener valores distintos o incluso no nulos.

Por supuesto, SQL Server 2012 tiene objetos de secuencia, por lo que hay una solución natural en ese producto.

Esto es lo que se me ocurrió:

 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 

Genera hasta 2 ^ 24 valores. Unirse a las condiciones mantenerlo rápido para valores pequeños.

Esto completó para mí en 36 segundos en nuestro servidor DEV. Al igual que la respuesta de Brian, enfocarse en filtrar al rango es importante desde dentro de la consulta; en BETWEEN aún intenta generar todos los registros iniciales antes del límite inferior, aunque no los necesite.

 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 

Tenga en cuenta que ROW_NUMBER es un bigint , por lo que no podemos pasar más de 2 ^^ 64 (== 16 ^^ 16) registros generados con cualquier método que lo use. Por lo tanto, esta consulta respeta el mismo límite superior en los valores generados.

Esto usa código de procedimiento y una función con valores de tabla. Lento, pero fácil y predecible.

 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 

Es una tabla, por lo que puedes usarla en combinaciones con otros datos. Con mayor frecuencia uso esta función como el lado izquierdo de una unión contra un GRUPO POR hora, día, etc. para asegurar una secuencia contigua de valores de tiempo.

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

El rendimiento es poco inspirador (16 segundos para un millón de filas) pero lo suficientemente bueno para muchos propósitos.

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