使用SQL产生无限条顺序数字(1-1000000...)的记录
很多时候需要在查询时产生顺序的数字记录,下面两个方法非常实用:
1.利用系统表
2.利用CTE
1.利用系统表
;with t as
(
SELECT
x = ROW_NUMBER() OVER
(ORDER BY s1.[object_id])
FROM sys.objects AS s1
CROSS JOIN sys.objects AS s2
CROSS JOIN sys.objects AS s3
)
select * from t where x BETWEEN 1 AND 100000
1000000可以替换成更大或更小的数 (
SELECT
x = ROW_NUMBER() OVER
(ORDER BY s1.[object_id])
FROM sys.objects AS s1
CROSS JOIN sys.objects AS s2
CROSS JOIN sys.objects AS s3
)
select * from t where x BETWEEN 1 AND 100000
2.利用CTE
SET NOCOUNT ON;
DECLARE @UpperLimit INT;
SET @UpperLimit = 1000000;
WITH
n5 (x) AS (SELECT 1 UNION SELECT 0),
n4 (x) AS (SELECT 1 FROM n5 CROSS JOIN n5 AS x),
n3 (x) AS (SELECT 1 FROM n4 CROSS JOIN n4 AS x),
n2 (x) AS (SELECT 1 FROM n3 CROSS JOIN n3 AS x),
n1 (x) AS (SELECT 1 FROM n2 CROSS JOIN n2 AS x),
n0 (x) AS (SELECT 1 FROM n1 CROSS JOIN n1 AS x),
Nbrs (x) AS
(
SELECT
ROW_NUMBER() OVER
(ORDER BY x)
FROM n0
)
SELECT [Number] = x
--INTO dbo.Numbers
FROM Nbrs
WHERE x BETWEEN 1 AND @UpperLimit;
同样@UpperLimit可以修改成其他数字。
DECLARE @UpperLimit INT;
SET @UpperLimit = 1000000;
WITH
n5 (x) AS (SELECT 1 UNION SELECT 0),
n4 (x) AS (SELECT 1 FROM n5 CROSS JOIN n5 AS x),
n3 (x) AS (SELECT 1 FROM n4 CROSS JOIN n4 AS x),
n2 (x) AS (SELECT 1 FROM n3 CROSS JOIN n3 AS x),
n1 (x) AS (SELECT 1 FROM n2 CROSS JOIN n2 AS x),
n0 (x) AS (SELECT 1 FROM n1 CROSS JOIN n1 AS x),
Nbrs (x) AS
(
SELECT
ROW_NUMBER() OVER
(ORDER BY x)
FROM n0
)
SELECT [Number] = x
--INTO dbo.Numbers
FROM Nbrs
WHERE x BETWEEN 1 AND @UpperLimit;