先新建一个表
IF OBJECT_ID('dbo.num', 'U')IS NOT NULL DROP TABLE dbo.num;
CREATE TABLE dbo.num(num INT NOT NULL PRIMARY KEY);
INSERT INTO dbo.num(num)
VALUES(0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
如果要生成1-1000的整数序列。可以对num表的三个实例进行交叉连接,每个实例代表10的不同次幂(1, 10, 100),每个实例的数字诚意它所代表的10的幂,再把结果加起来,最后加1.
SELECT D3.num * 100 + D2.num * 10 + D1.num + 1 as n
FROM num as D1
CROSS JOIN num as D2
CROSS JOIN num as D3
order by n;