SQL Server获取两日期区间全部日期 除左连临时日历表有其他方法吗

你当前使用WHILE循环逐行插入临时表生成日期的方案，在日期跨度较大时性能损耗很高，以下是三种更优的实现方案，可根据使用场景选择：

方案1：递归CTE动态生成日期序列（无需预建表，适合临时查询） #

无需提前创建临时表，单次查询直接生成目标日期区间，性能远高于循环插入，代码轻量化。

DECLARE @startDate DATE = '2021-09-05', @endDate DATE = '2021-09-15';

WITH calendarCTE AS (
    SELECT @startDate AS calendarDate
    UNION ALL
    SELECT DATEADD(DAY, 1, calendarDate)
    FROM calendarCTE
    WHERE calendarDate < @endDate
)
SELECT *
FROM calendarCTE td
LEFT JOIN dataTable dt WITH (NOLOCK) ON dt.reportDate = td.calendarDate
-- 日期跨度超过100天时必须加以下配置取消递归深度限制
OPTION (MAXRECURSION 0);

方案2：永久日历维度表（适合生产环境高频查询场景） #

如果业务中频繁需要生成跨日期报表，建议一次创建永久日历表，终身复用，还可扩展多维度日期属性，性能是所有方案中最优的。

建表&初始化代码（仅执行一次） #

-- 创建日历维度表
CREATE TABLE dimCalendar (
    calendarDate DATE PRIMARY KEY,
    year INT,
    month INT,
    day INT,
    weekOfYear INT,
    isWeekend BIT
    -- 可按需扩展是否节假日、所属季度等属性
);

-- 批量初始化100年日期
DECLARE @start DATE = '2020-01-01', @end DATE = '2119-12-31';
WITH calendarCTE AS (
    SELECT @start AS calendarDate
    UNION ALL
    SELECT DATEADD(DAY, 1, calendarDate)
    FROM calendarCTE
    WHERE calendarDate < @end
)
INSERT INTO dimCalendar (calendarDate, year, month, day, weekOfYear, isWeekend)
SELECT 
    calendarDate,
    YEAR(calendarDate),
    MONTH(calendarDate),
    DAY(calendarDate),
    DATEPART(WEEK, calendarDate),
    CASE WHEN DATEPART(WEEKDAY, calendarDate) IN (1,7) THEN 1 ELSE 0 END
FROM calendarCTE
OPTION (MAXRECURSION 0);

后续查询代码 #

SELECT *
FROM dimCalendar td WITH (NOLOCK)
LEFT JOIN dataTable dt WITH (NOLOCK) ON dt.reportDate = td.calendarDate
WHERE td.calendarDate BETWEEN '2021-09-05' AND '2021-09-15';

方案3：数字辅助表生成日期（适合大跨度日期动态查询） #

如果不想维护永久日历表，又需要频繁生成大跨度日期序列，可提前创建数字辅助表，生成日期的性能高于递归CTE，无递归深度限制。

建数字辅助表（仅执行一次） #

-- 存储0~100000的数字，可覆盖超过270年的日期跨度
CREATE TABLE nums (n INT PRIMARY KEY);
WITH t1 AS (SELECT 1 n UNION ALL SELECT 1),
t2 AS (SELECT 1 n FROM t1 a, t1 b),
t3 AS (SELECT 1 n FROM t2 a, t2 b),
t4 AS (SELECT 1 n FROM t3 a, t3 b),
t5 AS (SELECT 1 n FROM t4 a, t4 b),
numsCTE AS (SELECT ROW_NUMBER() OVER(ORDER BY n) -1 AS n FROM t5)
INSERT INTO nums SELECT n FROM numsCTE WHERE n <= 100000;

查询代码 #

DECLARE @startDate DATE = '2021-09-05', @endDate DATE = '2021-09-15';
SELECT *
FROM (
    SELECT DATEADD(DAY, n, @startDate) AS calendarDate
    FROM nums
    WHERE n <= DATEDIFF(DAY, @startDate, @endDate)
) td
LEFT JOIN dataTable dt WITH (NOLOCK) ON dt.reportDate = td.calendarDate;

方案选择建议 #

• 临时单次查询、日期跨度小于1000天：选递归CTE方案，无需预建任何表
• 生产环境高频生成日期类报表：选永久日历表方案，性能最优、扩展性最强
• 无永久表维护权限、经常查询大跨度日期：选数字辅助表方案

内容的提问来源于stack exchange，提问作者user2046734