SQL Server 2019如何合并默认与覆盖的日期区间？

问题场景 #

我有两个包含日期范围的表：

• 默认表：存储默认RECORDID及其生效的日期范围
• 覆盖表：存储覆盖RECORDID，其指定的日期范围会覆盖对应时段的默认RECORDID（无覆盖时使用默认值），且覆盖表的日期范围均落在默认表的区间内

默认表数据 #

STARTDATE          | ENDDATE            | RECORDID
__________________________________________________
2022/Nov/01 00:00  | 2022/Nov/30 00:00  | 10
2022/Dec/01 00:00  | 2022/Dec/31 00:00  | 16

覆盖表数据 #

STARTDATE          | ENDDATE            | RECORDID
__________________________________________________
2022/Nov/14 00:00  | 2022/Nov/16 00:00  | 12
2022/Dec/06 00:00  | 2022/Dec/20 00:00  | 18

期望合并结果 #

STARTDATE          | ENDDATE            | RECORDID
__________________________________________________
2022/Nov/01 00:00  | 2022/Nov/14 00:00  | 10
2022/Nov/14 00:00  | 2022/Nov/16 00:00  | 12
2022/Nov/16 00:00  | 2022/Nov/30 00:00  | 10
2022/Dec/01 00:00  | 2022/Dec/06 00:00  | 16
2022/Dec/06 00:00  | 2022/Dec/20 00:00  | 18
2022/Dec/20 00:00  | 2022/Dec/31 00:00  | 16

高效实现方案 #

采用基于集合运算的CTE（公共表表达式）方案，避免循环操作，在SQL Server 2019中性能更优：

步骤1：创建测试表（可选，用于验证） #

-- 创建默认表
CREATE TABLE DefaultRecords (
    STARTDATE DATETIME,
    ENDDATE DATETIME,
    RECORDID INT
);

INSERT INTO DefaultRecords VALUES
('2022-11-01 00:00:00', '2022-11-30 00:00:00', 10),
('2022-12-01 00:00:00', '2022-12-31 00:00:00', 16);

-- 创建覆盖表
CREATE TABLE OverrideRecords (
    STARTDATE DATETIME,
    ENDDATE DATETIME,
    RECORDID INT
);

INSERT INTO OverrideRecords VALUES
('2022-11-14 00:00:00', '2022-11-16 00:00:00', 12),
('2022-12-06 00:00:00', '2022-12-20 00:00:00', 18);

步骤2：核心查询语句 #

WITH DatePoints AS (
    -- 提取所有关键日期点：默认表和覆盖表的开始、结束日期
    SELECT STARTDATE AS PointDate FROM DefaultRecords
    UNION
    SELECT ENDDATE AS PointDate FROM DefaultRecords
    UNION
    SELECT STARTDATE AS PointDate FROM OverrideRecords
    UNION
    SELECT ENDDATE AS PointDate FROM OverrideRecords
),
OrderedDates AS (
    -- 按日期排序，为每个日期点生成下一个连续日期点
    SELECT 
        PointDate,
        LEAD(PointDate) OVER (ORDER BY PointDate) AS NextPointDate
    FROM DatePoints
),
RangeAssignments AS (
    -- 为每个拆分后的小区间匹配默认ID和覆盖ID
    SELECT
        od.PointDate AS STARTDATE,
        od.NextPointDate AS ENDDATE,
        dr.RECORDID AS DefaultRecordId,
        orr.RECORDID AS OverrideRecordId
    FROM OrderedDates od
    JOIN DefaultRecords dr
        ON od.PointDate >= dr.STARTDATE AND od.NextPointDate <= dr.ENDDATE
    LEFT JOIN OverrideRecords orr
        ON od.PointDate >= orr.STARTDATE AND od.NextPointDate <= orr.ENDDATE
)
-- 优先使用覆盖ID，无覆盖则用默认ID，过滤无效区间
SELECT
    STARTDATE,
    ENDDATE,
    COALESCE(OverrideRecordId, DefaultRecordId) AS RECORDID
FROM RangeAssignments
WHERE NextPointDate IS NOT NULL
ORDER BY STARTDATE;

方案优势 #

• 基于集合运算替代循环，数据量越大性能优势越明显
• 通过提取关键日期点拆分区间，逻辑清晰易维护
• 使用LEAD窗口函数简化连续区间生成，避免复杂的关联逻辑
• 利用COALESCE快速实现"覆盖优先"的取值规则

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