SQL Server 2016无数据库对象生成全日期范围T-SQL查询咨询

可行方案说明 #

首先假设你的源表结构为SourceInterval，包含3个核心字段：BizTag varchar(10)（存储ABC/HDF等标识）、IntervalStart date（区间开始日期）、IntervalEnd date（区间结束日期）。

方案1：输出逐日期覆盖状态表 #

输出覆盖输入区间的所有日期，每行对应一个日期+一个标识，标注是否被源区间覆盖，完全符合你要求的纯派生表语法限制：

SELECT * 
FROM
    (
    -- 生成全日期+所有标识的笛卡尔积基础表
    SELECT 
        DateList.FullDate,
        TagList.BizTag,
        CASE WHEN s.IntervalStart IS NOT NULL THEN '已覆盖' ELSE '未覆盖' END AS CoverStatus
    FROM
        -- 派生表1：用系统表生成输入区间内的所有连续日期
        (
        SELECT DATEADD(DAY, number, @RangeStart) AS FullDate
        FROM master..spt_values
        WHERE type = 'P' 
        AND number <= DATEDIFF(DAY, @RangeStart, @RangeEnd)
        ) DateList
    CROSS JOIN
        -- 派生表2：取源表所有不重复的业务标识
        (SELECT DISTINCT BizTag FROM SourceInterval) TagList
    LEFT JOIN SourceInterval s
        ON TagList.BizTag = s.BizTag
        AND DateList.FullDate BETWEEN s.IntervalStart AND s.IntervalEnd
    ) DerivedTable

查询结果会包含输入区间内的所有日期，每个日期对应所有标识的覆盖状态，不需要依赖任何自定义的临时表、表变量或CTE。

方案2：输出每个标识的连续覆盖/缺失区间表 #

如果需要输出每个标识的连续区间汇总，可通过多层派生表嵌套实现：

SELECT * 
FROM
    (
    SELECT 
        BizTag,
        MIN(FullDate) AS RangeStart,
        MAX(FullDate) AS RangeEnd,
        CoverStatus
    FROM
        (
        SELECT 
            DateList.FullDate,
            TagList.BizTag,
            CASE WHEN s.IntervalStart IS NOT NULL THEN '已覆盖' ELSE '未覆盖' END AS CoverStatus,
            -- 用日期减行号实现连续区间分组
            DATEADD(DAY, -ROW_NUMBER() OVER(PARTITION BY TagList.BizTag, CASE WHEN s.IntervalStart IS NOT NULL THEN 1 ELSE 0 END ORDER BY DateList.FullDate), DateList.FullDate) AS GroupId
        FROM
            (
            SELECT DATEADD(DAY, number, @RangeStart) AS FullDate
            FROM master..spt_values
            WHERE type = 'P' 
            AND number <= DATEDIFF(DAY, @RangeStart, @RangeEnd)
            ) DateList
        CROSS JOIN (SELECT DISTINCT BizTag FROM SourceInterval) TagList
        LEFT JOIN SourceInterval s
            ON TagList.BizTag = s.BizTag
            AND DateList.FullDate BETWEEN s.IntervalStart AND s.IntervalEnd
        ) T
    GROUP BY BizTag, CoverStatus, GroupId
    ) DerivedTable

该查询会输出每个标识的连续覆盖/缺失区间，比如RST的两段4月覆盖区间会分成两行输出，未覆盖的区间也会单独列出。

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