我有两个数字作为用户的输入,例如 1000
和1050
.
如何在单独的行中使用SQL查询生成这两个数字之间的数字?我要这个:
1000 1001 1002 1003 . . 1050
slartidan.. 136
使用VALUES
关键字选择非持久值.然后使用JOIN
s生成许多组合(可以扩展以创建数十万行甚至更多).
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) WHERE ones.n + 10*tens.n + 100*hundreds.n + 1000*thousands.n BETWEEN @userinput1 AND @userinput2 ORDER BY 1
Demo
一个较短的选择,这不容易理解:
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
我使用的最佳选择如下:
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
我已经使用它创建了数百万条记录,并且它完美无缺.
我最近写了这个内联表值函数来解决这个问题.它不限于内存和存储以外的范围.它不访问任何表,因此通常不需要磁盘读取或写入.它在每次迭代时以指数方式添加连接值,因此即使对于非常大的范围也非常快.它在我的服务器上在五秒内创建了一千万条记录.它也适用于负值.
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'));
您可以对其使用交叉应用连接,以根据表中的值拆分记录.因此,例如,为了在表格中的时间范围内创建每分钟的记录,您可以执行以下操作:
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
SELECT DISTINCT n = number FROM master..[spt_values] WHERE number BETWEEN @start AND @end
Demo
请注意,此表的最大值为2048,因为这些数字有间隙.
这是一个使用系统视图的更好的方法(从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
或使用自定义数字表.致Aaron Bertrand,我建议阅读整篇文章:生成一个没有循环的集合或序列
使用VALUES
关键字选择非持久值.然后使用JOIN
s生成许多组合(可以扩展以创建数十万行甚至更多).
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) WHERE ones.n + 10*tens.n + 100*hundreds.n + 1000*thousands.n BETWEEN @userinput1 AND @userinput2 ORDER BY 1
Demo
一个较短的选择,这不容易理解:
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
最好的方法是使用递归ctes.
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.
如果您在服务器中安装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() { } } }
将程序集放在服务器上的某个位置并运行:
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)
没有什么新东西,但我重写了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
另一种解决方案是递归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)
2年后,但我发现我遇到了同样的问题.这是我如何解决它.(编辑包含参数)
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)
它对我有用!
select top 50 ROW_NUMBER() over(order by a.name) + 1000 as Rcount from sys.all_objects a
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