Sqlserver生成自然数序列并生成日历的几种方法

方法一：

SELECT NUMBER FROM MASTER..SPT_VALUES WHERE TYPE='P' and NUMBER BETWEEN 1 AND 100

方法二：
WITH CTE_NUMBER AS
(SELECT n FROM (VALUES(0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) NUMBER(n))
SELECT (100T1.n+10T2.n+T3.n)+1 as n FROM CTE_NUMBER T1
CROSS JOIN CTE_NUMBER T2
CROSS JOIN CTE_NUMBER T3
ORDER BY n

方法三：
IF OBJECT_ID(‘dbo.T1’,‘U’) IS NOT NULL DROP TABLE dbo.T1;
CREATE TABLE dbo.T1(n INT IDENTITY);
GO
INSERT INTO dbo.T1 DEFAULT VALUES;
GO 100
SELECT * FROM dbo.T1

方法四：
DECLARE @i INT=0
WHILE @i<101
BEGIN
IF OBJECT_ID(‘dbo.T1’,‘U’) IS NULL
SELECT n=@i INTO T1
ELSE
INSERT INTO T1 VALUES(@i)
SET @i=@i+1
END
SELECT * FROM T1

方法五：
WITH CTE_N AS
(
SELECT 1 AS N
UNION ALL
SELECT N=N+1 FROM CTE_N WHERE N<200
)
SELECT * FROM CTE_N OPTION (MAXRECURSION 200)

最后,任选一方法生成日历:

SELECT DATEADD(DAY,NUMBER,CAST(GETDATE() as DATE)) as DAY_LINE
FROM (SELECT NUMBER FROM MASTER..SPT_VALUES WHERE TYPE='P' and NUMBER BETWEEN 1 AND 100) N_LINE
WHERE NUMBER < 101