[SQL] N개 행 생성하기

숫자 테이블을 만들어 두는 것이 가장 좋지만 쿼리만으로 생성하고자 할 때

 

 

1.시스템 테이블 사용 (가장 빠름)

SELECT * FROM
    (SELECT ROW_NUMBER() OVER(ORDER BY (SELECT 0)) AS no 
        FROM sys.all_objects a, sys.all_objects b, sys.all_objects c -- 100억
    ) a
WHERE no < 1000


--MSSQL
SELECT TOP 1000 ROW_NUMBER() OVER(ORDER BY (SELECT 0)) AS no 
    FROM sys.all_objects a, sys.all_objects b, sys.all_objects c -- 100억

뷰로 만들어 쓰기

CREATE VIEW V_Numbers AS
    SELECT ROW_NUMBER() OVER(ORDER BY (SELECT 1)) AS no
    FROM sys.all_objects a, sys.all_objects b, sys.all_objects c
    
GO

SELECT * FROM V_Numbers WHERE no < 1000

테이블 타입 함수로 만들어 쓰기 (MSSQL)

CREATE FUNCTION Numbers(@cnt int)
RETURNS TABLE 
AS
RETURN 
    SELECT TOP(@cnt) ROW_NUMBER() OVER(ORDER BY (SELECT 1)) AS no
    FROM sys.all_objects a, sys.all_objects b, sys.all_objects c

GO

SELECT no FROM NumRows(1000)

 

 

2.테이블 없이 WITH 절 사용 (Cross join)

WITH A AS
(
    SELECT 1 no UNION ALL
    SELECT no + 1 FROM A WHERE no < 100
),
B AS 
(
    SELECT ROW_NUMBER() OVER (ORDER BY (SELECT 0)) AS no 
    FROM a a1, a a2, a a3, a a4  -- 1억
)
SELECT count(*) FROM B 
    WHERE no < 1000

 

3.WITH 절 사용 (재귀 쿼리)

 

WITH 
    a AS (SELECT 1 no UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1),
    b AS (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT 0)) AS no FROM a, a a2, a a3, a a4),
    c AS (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT 0)) AS no FROM b, b b2, b b3) -- 2.4억
SELECT no FROM c WHERE no < 1000