SQL 프런티어 컬럼
![[레벨:100]](http://www.sqler.com/modules/point/icons/warrock_17x17/100.gif "포인트:2005239point, 레벨:100/100"){kind=icon}
석이Itzik님의 function 입니다.
IF OBJECT_ID('dbo.GetNums', 'IF') IS NOT NULL
DROP FUNCTION dbo.GetNums;
GO
CREATE FUNCTION dbo.GetNums(@n AS BIGINT) RETURNS TABLE
AS
RETURN
WITH
L0 AS(SELECT 1 AS c UNION ALL SELECT 1),
L1 AS(SELECT 1 AS c FROM L0 AS A CROSS JOIN L0 AS B),
L2 AS(SELECT 1 AS c FROM L1 AS A CROSS JOIN L1 AS B),
L3 AS(SELECT 1 AS c FROM L2 AS A CROSS JOIN L2 AS B),
L4 AS(SELECT 1 AS c FROM L3 AS A CROSS JOIN L3 AS B),
L5 AS(SELECT 1 AS c FROM L4 AS A CROSS JOIN L4 AS B),
Nums AS(SELECT ROW_NUMBER() OVER(ORDER BY (SELECT 0)) AS n FROM L5)
SELECT n FROM Nums WHERE n <= @n;
GO
select * from dbo.GetNums(1000)
select top 1 email from world where hobby = 'sql' and sqlguru = 1 order by sqllevel desc;
김민석 / SQL 프런티어 1기 / http://www.sqler.com / http://cafe.naver.com/sqlmvp
SELECT VALUE FROM
( SELECT ((digit1 * 10) + digit2) * 10 + digit3 AS VALUE
FROM (
select 0 as digit1 union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) AS d1
CROSS JOIN (select 0 as digit2 union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) AS d2
CROSS JOIN (select 0 as digit3 union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) AS d3) AS rs
ORDER BY VALUE
전 위와 같은 쿼리가 좋습니다.
이유는 수학적 간소화로 이해하기가 쉽고 응용이 가능하기 때문입니다. = (A * 10) + B
올만에 덧글을 다니 보는 것 보다 힘듭니다. ㅎㅎ 그러고 보니 늘 하루가 멀다 글을 올리는 분들 참 감사합니다.
이런 방법도 있군요!!