SQL Server Express中获取当前合作关系各领域起始日期的查询方案

示例表结构与测试数据 #

先基于需求定义合作关系表Partnerships及测试数据（可直接在SQL Server Express中执行）：

CREATE TABLE Partnerships (
    PartnerID INT,
    Domain VARCHAR(50),
    StartDate DATE,
    EndDate DATE NULL -- NULL表示当前合作处于有效状态
);

INSERT INTO Partnerships VALUES
(1, 'Tech', '2020-01-01', '2022-06-30'),
(1, 'Tech', '2023-01-01', NULL),
(1, 'Marketing', '2021-03-01', NULL),
(2, 'Finance', '2019-05-01', '2020-12-31'),
(2, 'Finance', '2021-04-01', '2023-02-28'),
(2, 'Finance', '2023-03-01', NULL),
(3, 'Legal', '2022-01-01', NULL);

单条SQL查询方案 #

通过窗口函数实现需求，无需额外建表或循环操作：

WITH PartnershipHistory AS (
    SELECT 
        PartnerID,
        Domain,
        StartDate,
        EndDate,
        -- 获取同合作方同领域的上一次合作结束日期
        LAG(EndDate) OVER (PARTITION BY PartnerID, Domain ORDER BY StartDate) AS PreviousEndDate,
        -- 标记当前记录是否为中断后的恢复合作
        CASE 
            WHEN LAG(EndDate) OVER (PARTITION BY PartnerID, Domain ORDER BY StartDate) IS NOT NULL 
                 AND StartDate > LAG(EndDate) OVER (PARTITION BY PartnerID, Domain ORDER BY StartDate)
            THEN 1 
            ELSE 0 
        END AS IsResumed,
        -- 标记是否为当前有效合作
        CASE WHEN EndDate IS NULL THEN 1 ELSE 0 END AS IsCurrent
    FROM Partnerships
),
PartnerDomainStats AS (
    SELECT 
        PartnerID,
        Domain,
        -- 统计该合作方在对应领域是否存在中断恢复记录
        MAX(IsResumed) AS HasResumedHistory
    FROM PartnershipHistory
    GROUP BY PartnerID, Domain
)
SELECT 
    ph.PartnerID,
    ph.Domain,
    ph.StartDate AS CurrentResumedStartDate
FROM PartnershipHistory ph
JOIN PartnerDomainStats pds ON ph.PartnerID = pds.PartnerID AND ph.Domain = pds.Domain
WHERE ph.IsCurrent = 1 AND pds.HasResumedHistory = 1
ORDER BY ph.PartnerID, ph.Domain;

逻辑说明 #

1. PartnershipHistory CTE：利用LAG窗口函数回溯同组的上一条合作记录，判断当前合作是否为中断后恢复，同时标记当前有效合作。
2. PartnerDomainStats CTE：按合作方+领域分组，筛选出有过中断恢复历史的合作组。
3. 最终查询：提取当前有效且有中断恢复历史的合作记录，返回其当前起始日期。

预期结果 #

执行查询后将得到如下结果：

内容的提问来源于stack exchange，提问作者Hugh Self Taught