Comment générer une plage de nombres entre deux nombres?

Vous pouvez sélectionner des valeurs imaginaires en utilisant le mot-key VALUES . Certains JOIN génèrent alors beaucoup de combinaisons (peuvent être étendues pour créer des centaines de milliers de lignes).

Vos numbers (de 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 

Démo

Avantages:

• Rapide comme l'éclair
• Pas de limites
• Sorte de lisible
• Peut être utilisé à l'intérieur des vues

Les inconvénients:

• très gros nombres nécessitent beaucoup de jointures

Un autre exemple, générant des nombres de 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 

Démo

Une alternative plus courte, mais pas aussi facile à lire:

 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 

Démo

une solution alternative est CTE récursif:

 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 

Démo

Notez que cette table a un maximum de 2048 parce que les nombres ont des lacunes.

Voici une approche légèrement meilleure en utilisant une vue système (depuis 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; 

Démo

ou utilisez une table numérique personnalisée. Crédits à Aaron Bertrand, je suggère de lire l'article entier: Générer un set ou une séquence sans loops

J'ai récemment écrit cette fonction inline table valorisée pour résoudre ce problème. Il n'est pas limité dans la gamme autre que la memory et le stockage. Il n'accède à aucune table, il n'y a donc pas besoin de lectures ou d'écritures de disque en général. Il ajoute des valeurs de jointures de manière exponentielle à chaque itération, ce qui fait qu'il est très rapide même pour de très grandes plages. Il crée dix millions d'loggings en cinq secondes sur mon server. Cela fonctionne également avec des valeurs négatives.

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

Il est également pratique pour les plages de dates et d'heures:

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

Vous pouvez utiliser une jointure d'application croisée pour split les loggings en fonction des valeurs de la table. Ainsi, par exemple, pour créer un logging pour chaque minute sur une plage de time dans un tableau, vous pouvez faire quelque chose comme:

 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 meilleure option que j'ai utilisée est la suivante:

 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 

J'ai généré des millions d'loggings en utilisant ceci et cela fonctionne parfaitement.

Si vous ne rencontrez pas de problème lors de l'installation d'un assemblage CLR sur votre server, une bonne option consiste à écrire une fonction de valeur table dans .NET. De cette façon, vous pouvez utiliser une syntaxe simple, ce qui facilite la jointure avec d'autres requêtes et, en prime, ne gaspillera pas de memory car le résultat est diffusé.

Créez un projet contenant la class suivante:

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

Placez l'assembly quelque part sur le server et exécutez:

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

Maintenant vous pouvez exécuter:

 select * from dbo.seq(1, 1000000) 

Voici quelques solutions assez optimales et 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 ; 

2 ans plus tard, mais j'ai trouvé que j'avais le même problème. Voici comment je l'ai résolu. (édité pour inclure les 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) 

Cela fera aussi

 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 réponse de slartidan peut être améliorée, en termes de performances, en supprimant toutes les references au produit cartésien et en utilisant ROW_NUMBER() place ( plan d'exécution comparé ):

 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 

Enveloppez-le dans un CTE et ajoutez une clause where pour sélectionner les nombres désirés:

 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 

Ca marche pour moi!

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

La meilleure vitesse lors de l'exécution de la requête

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

J'ai dû insert le path du file image dans la database en utilisant une méthode similaire. La requête ci-dessous a bien fonctionné:

 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 

Le code pour vous serait:

 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 

Cela ne fonctionne que pour les séquences tant que certaines tables d'application ont des lignes. Supposons que je veux la séquence de 1..100, et ai la table d'application dbo.foo avec la colonne (de type numérique ou string) foo.bar:

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

Malgré sa présence dans une clause order by, dbo.foo.bar n'a pas besoin d'avoir des valeurs distinctes ou même non nulles.

Bien sûr, SQL Server 2012 a des objects de séquence, il y a donc une solution naturelle dans ce produit.

CTE récursif en taille exponentielle (même pour une récursivité par défaut de 100, cela peut générer jusqu'à 2 ^ 100 nombres):

 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