如何生成指定日期区间的年/月/日序列用于表关联查询？

Hey Dan, 刚好能帮你解决这个需求！你想要的是生成指定起止日期范围内的每日日期序列，以此为基础关联表A和表B，确保所有日期都能显示——哪怕没有匹配的关联数据也返回null或0对吧？下面给你一步步拆解实现方案：

我们可以用两种方式实现，一种是和你提供的小时生成逻辑类似，利用系统表master.dbo.spt_values；另一种是用递归CTE，适合更长的日期区间。

方法1：利用master.dbo.spt_values（适合短区间） #

这个方法和你给的小时生成例子逻辑一致，通过计算起止日期的天数差，生成对应的每日序列并拆分年、月、日：

DECLARE @StartDate DATE = '2023-01-01',
        @EndDate DATE = '2023-01-10';

SELECT 
    DATEADD(day, number, @StartDate) AS FullDate,
    YEAR(DATEADD(day, number, @StartDate)) AS [YEAR],
    MONTH(DATEADD(day, number, @StartDate)) AS [MONTH],
    DAY(DATEADD(day, number, @StartDate)) AS [DAY]
FROM master.dbo.spt_values
WHERE 
    type = 'P' -- 只取正数序列
    AND number <= DATEDIFF(day, @StartDate, @EndDate);

注意：master.dbo.spt_values的number字段最大值是2047，所以如果你的日期区间超过2047天，这个方法就不适用了，建议用下面的递归CTE方案。

方法2：递归CTE（适合任意长度区间） #

递归CTE可以生成任意长度的日期序列，不受天数限制：

DECLARE @StartDate DATE = '2023-01-01',
        @EndDate DATE = '2023-02-10';

WITH DateSequence AS (
    -- 起始日期
    SELECT 
        @StartDate AS FullDate,
        YEAR(@StartDate) AS [YEAR],
        MONTH(@StartDate) AS [MONTH],
        DAY(@StartDate) AS [DAY]
    UNION ALL
    -- 递归生成后续日期
    SELECT 
        DATEADD(day, 1, FullDate),
        YEAR(DATEADD(day, 1, FullDate)),
        MONTH(DATEADD(day, 1, FullDate)),
        DAY(DATEADD(day, 1, FullDate))
    FROM DateSequence
    WHERE FullDate < @EndDate
)
SELECT * FROM DateSequence OPTION (MAXRECURSION 0); -- 取消递归次数限制

用LEFT JOIN关联你的业务表，这样即使表A或表B没有对应日期的数据，也能保留日期序列的记录，并用ISNULL将空值替换为0或null：

DECLARE @StartDate DATE = '2023-01-01',
        @EndDate DATE = '2023-01-10';

WITH DateSequence AS (
    SELECT 
        DATEADD(day, number, @StartDate) AS FullDate,
        YEAR(DATEADD(day, number, @StartDate)) AS [YEAR],
        MONTH(DATEADD(day, number, @StartDate)) AS [MONTH],
        DAY(DATEADD(day, number, @StartDate)) AS [DAY]
    FROM master.dbo.spt_values
    WHERE 
        type = 'P'
        AND number <= DATEDIFF(day, @StartDate, @EndDate)
)
SELECT 
    ds.[YEAR],
    ds.[MONTH],
    ds.[DAY],
    ISNULL(a.A_Value, 0) AS A_Value, -- 无匹配时显示0
    ISNULL(b.B_Value, NULL) AS B_Value -- 无匹配时显示null
FROM DateSequence ds
LEFT JOIN TableA a ON ds.FullDate = a.A_Date -- 关联表A的日期字段
LEFT JOIN TableB b ON ds.FullDate = b.B_Date -- 关联表B的日期字段
ORDER BY ds.FullDate;

如果需要多次使用这个日期序列生成逻辑，可以把它封装成表值函数，方便调用：

CREATE FUNCTION dbo.GetDateSequence(@StartDate DATE, @EndDate DATE)
RETURNS TABLE
AS
RETURN (
    WITH DateSequence AS (
        SELECT 
            @StartDate AS FullDate,
            YEAR(@StartDate) AS [YEAR],
            MONTH(@StartDate) AS [MONTH],
            DAY(@StartDate) AS [DAY]
        UNION ALL
        SELECT 
            DATEADD(day, 1, FullDate),
            YEAR(DATEADD(day, 1, FullDate)),
            MONTH(DATEADD(day, 1, FullDate)),
            DAY(DATEADD(day, 1, FullDate))
        FROM DateSequence
        WHERE FullDate < @EndDate
    )
    SELECT * FROM DateSequence OPTION (MAXRECURSION 0)
);

调用函数的方式很简单：

SELECT 
    ds.[YEAR],
    ds.[MONTH],
    ds.[DAY],
    ISNULL(a.A_Value, 0) AS A_Value,
    ISNULL(b.B_Value, NULL) AS B_Value
FROM dbo.GetDateSequence('2023-01-01', '2023-01-10') ds
LEFT JOIN TableA a ON ds.FullDate = a.A_Date
LEFT JOIN TableB b ON ds.FullDate = b.B_Date
ORDER BY ds.FullDate;

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