[sql] 두 숫자 사이의 숫자 범위를 생성하는 방법은 무엇입니까?

Program / 글쓴이

사용자의 입력으로 두 개의 숫자가 있습니다 (예 : 1000및) 1050.

별도의 행에서 SQL 쿼리를 사용 하여이 두 숫자 사이의 숫자를 어떻게 생성합니까? 나는 이것을 원한다 :

 1000
 1001
 1002
 1003
 .
 .
 1050

답변

VALUES키워드를 사용하여 비 지속 값을 선택하십시오 . 그런 다음 JOINs를 사용 하여 많은 조합을 생성하십시오 (수십만 행 이상으로 확장 가능).

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

답변

대체 솔루션은 재귀 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)

답변

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의 학점은 전체 기사를 읽는 것이 좋습니다. 루프없이 세트 또는 시퀀스 생성

답변

최근 에이 인라인 테이블 값 함수를 작성 하여이 문제를 해결했습니다. 메모리 및 스토리지 이외의 범위에는 제한이 없습니다. 테이블에 액세스하지 않으므로 일반적으로 디스크 읽기 또는 쓰기가 필요하지 않습니다. 각 반복마다 지수 적으로 결합 값을 추가하므로 매우 넓은 범위에서도 매우 빠릅니다. 내 서버에서 5 초 안에 천만 개의 레코드를 만듭니다. 음수 값으로도 작동합니다.

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

교차 적용 조인을 사용하여 테이블의 값을 기반으로 레코드를 분할 할 수 있습니다. 예를 들어 테이블의 시간 범위에서 1 분마다 레코드를 작성하려면 다음과 같이 할 수 있습니다.

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

답변

내가 사용한 가장 좋은 옵션은 다음과 같습니다.

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

이것을 사용하여 수백만 개의 레코드를 생성했으며 완벽하게 작동합니다.

답변

그것은 나를 위해 작동합니다!

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

답변

가장 좋은 방법은 재귀 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)

살루도.