数据库 日历表存储-with用法

--1.连续数字表
drop table Nums
CREATE TABLE Nums(n int NOT NULL PRIMARY KEY CLUSTERED)

WITH B1 AS(SELECT n=1 UNION ALL SELECT n=1), --2
B2 AS(SELECT n=1 FROM B1 a CROSS JOIN B1 b), --4
B3 AS(SELECT n=1 FROM B2 a CROSS JOIN B2 b), --16
B4 AS(SELECT n=1 FROM B3 a CROSS JOIN B3 b), --256
B5 AS(SELECT n=1 FROM B4 a CROSS JOIN B4 b), --65536
--..还可以继续,不过够用了
CTE AS(SELECT autonum=ROW_NUMBER() over(order by (select 1)) FROM B5 a CROSS JOIN B3 b) --65536 * 16
--select * from CTE
INSERT INTO Nums SELECT TOP(1000000) autonum FROM CTE --插入100万条数据

--2. 日历表
drop table calendar
CREATE TABLE Calendar(
date date NOT NULL PRIMARY KEY CLUSTERED,
weeknum int NOT NULL,
weekday int NOT NULL,
weekname nchar(3) NOT NULL,
workday bit NOT NULL
)

WITH CTE1 AS(
SELECT
date = DATEADD(day,n,‘19991231‘)
FROM Nums
WHERE n <= DATEDIFF(day,‘19991231‘,‘20501231‘)
),
CTE2 AS(
SELECT
date,
weeknum = DATEPART(week,date),
weekday = DATEPART(weekday,date)-1,
weekname = DATENAME(weekday,date)
FROM CTE1
)
--select * from CTE2
INSERT INTO Calendar
SELECT
date,
weeknum,
weekday,
weekname,
workday = CASE WHEN weekday IN (0,6) THEN 0 ELSE 1 END
FROM CTE2

--
select * from Calendar
select @@DATEFIRST