MS-SQL中输入新日期范围时自动调整日期区间的实现求助

嘿，这个日期区间拆分的需求我之前在处理订阅周期、合同有效期这类业务场景时经常碰到，刚好可以给你梳理下MS SQL里的实现思路。

核心逻辑 #

本质上我们需要将原有日期区间与输入的新日期区间进行比对，拆分出三个部分：

1. 原有区间中早于新区间的片段
2. 新输入的日期区间本身
3. 原有区间中晚于新区间的片段（如果存在）

场景1：单个无限期区间拆分 #

原有数据 #

输入新范围 #

10-02-2018 至 25-04-2018

实现SQL #

-- 定义输入的新日期范围
DECLARE @NewFrom DATE = '2018-02-10', @NewTo DATE = '2018-04-25';

-- 模拟原有数据表
WITH OriginalData AS (
    SELECT CAST('2018-01-01' AS DATE) AS FromDate, CAST('1900-01-01' AS DATE) AS ToDate
)
-- 拆分逻辑
SELECT 
    FromDate, 
    @NewFrom AS ToDate
FROM OriginalData
WHERE FromDate < @NewFrom

UNION ALL

-- 插入新的日期区间
SELECT 
    @NewFrom AS FromDate, 
    @NewTo AS ToDate

UNION ALL

-- 保留原有区间中晚于新区间的部分（处理无限期的特殊情况）
SELECT 
    @NewTo AS FromDate, 
    ToDate
FROM OriginalData
WHERE ToDate = '1900-01-01' OR @NewTo < ToDate;

执行结果 #

场景2：多个连续有限区间拆分 #

原有数据 #

示例输入新范围 #

15-03-2018 至 20-05-2018（覆盖前两个原有区间的部分范围）

实现SQL #

-- 定义输入的新日期范围
DECLARE @NewFrom DATE = '2018-03-15', @NewTo DATE = '2018-05-20';

-- 模拟原有数据表
WITH OriginalData AS (
    SELECT CAST('2018-01-01' AS DATE) AS FromDate, CAST('2018-04-25' AS DATE) AS ToDate
    UNION ALL
    SELECT CAST('2018-04-25' AS DATE) AS FromDate, CAST('2018-06-30' AS DATE) AS ToDate
    UNION ALL
    SELECT CAST('2018-06-30' AS DATE) AS FromDate, CAST('2018-08-05' AS DATE) AS ToDate
)
-- 1. 拆分原有区间中早于新区间的片段
SELECT 
    FromDate, 
    @NewFrom AS ToDate
FROM OriginalData
WHERE ToDate > @NewFrom AND FromDate < @NewFrom

UNION ALL

-- 2. 插入新的日期区间
SELECT @NewFrom, @NewTo

UNION ALL

-- 3. 拆分原有区间中晚于新区间的片段
SELECT 
    @NewTo AS FromDate, 
    ToDate
FROM OriginalData
WHERE FromDate < @NewTo AND ToDate > @NewTo

UNION ALL

-- 4. 保留完全不重叠的原有区间
SELECT 
    FromDate, 
    ToDate
FROM OriginalData
WHERE ToDate <= @NewFrom OR FromDate >= @NewTo;

执行结果 #

关键说明 #

• 代码中用UNION ALL来合并各个拆分片段，比UNION更高效（不需要去重）
• 针对无限期区间（比如用1900-01-01或9999-12-31表示永久有效），需要单独判断避免逻辑错误
• 这个逻辑可以适配绝大多数区间拆分场景，包括新区间完全覆盖原有区间、完全不重叠等情况

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