一、SQL Server
方法一、利用master..spt_values系统表
--最大2047 SELECT number FROM master..spt_values WHERE number>=1 AND number<=100 AND type='P'
方法二、利用CROSS JOIN
CROSS JOIN的结果集中数据行的数量是:左表数据行数和右表数据行数的乘积,由于每个TABLE都有10个数字(从0到9),4个TABLE进行CROSS JOIN能够快速产生10的4次方,即10000个顺序数字。
;WITH cte AS (SELECT n FROM(VALUES(0), (1), (2), (3), (4), (5), (6), (7), (8), (9)) AS p(n) ) SELECT a.n+b.n * 10+c.n * 100+d.n * 1000 AS n FROM cte a CROSS JOIN cte b CROSS JOIN cte c CROSS JOIN cte d ORDER BY n;
上面实现0-999,下面语句实现1-1000,大同小异:
;WITH cte AS (SELECT n FROM(VALUES(0), (1), (2), (3), (4), (5), (6), (7), (8), (9)) AS p(n) ) SELECT ROW_NUMBER() OVER (ORDER BY(SELECT 1)) id --(SELECT 1)可以替换为NEWID()或a.n FROM cte a CROSS APPLY cte b --CROSS APPL可以替换为CROSS JOIN CROSS APPLY cte c; --CROSS APPL可以替换为CROSS JOIN
二、MySQL
--最大518 SELECT @row:=@row+1 rownum FROM sys.metrics a,(SELECT @row:=0) b