获取分表存储起止日期的最新合作关系SQL解决方案

核心逻辑是通过P1和P2字段关联起始表与结束表，将对应合作事项的起止日期配对，同时可按需筛选当前有效或最新的合作关系。

1. 基础关联：匹配所有P1-P2组合的完整起止日期 #

直接通过P1和P2匹配两张表的记录，保留原表空值，生成符合要求的结果格式：

INSERT INTO #tblResult (P1, P2, SdateSt1, EdateSt1, SdateSt2, EdateSt2, SdateSt3, EdateSt3, SdateSt4, EdateSt4)
SELECT
    s.P1,
    s.P2,
    s.St1 AS SdateSt1,
    e.E1 AS EdateSt1,
    s.St2 AS SdateSt2,
    e.E2 AS EdateSt2,
    s.St3 AS SdateSt3,
    e.E3 AS EdateSt3,
    s.St4 AS SdateSt4,
    e.E4 AS EdateSt4
FROM #tblStart s
INNER JOIN #tblEnd e ON s.P1 = e.P1 AND s.P2 = e.P2;

2. 筛选当前有效合作（未过期） #

如果只需要保留尚未结束的合作关系，添加WHERE条件过滤过期记录：

INSERT INTO #tblResult (P1, P2, SdateSt1, EdateSt1, SdateSt2, EdateSt2, SdateSt3, EdateSt3, SdateSt4, EdateSt4)
SELECT
    s.P1,
    s.P2,
    s.St1 AS SdateSt1,
    e.E1 AS EdateSt1,
    s.St2 AS SdateSt2,
    e.E2 AS EdateSt2,
    s.St3 AS SdateSt3,
    e.E3 AS EdateSt3,
    s.St4 AS SdateSt4,
    e.E4 AS EdateSt4
FROM #tblStart s
INNER JOIN #tblEnd e ON s.P1 = e.P1 AND s.P2 = e.P2
WHERE 
    (e.E1 IS NULL OR e.E1 >= GETDATE())
    OR (e.E2 IS NULL OR e.E2 >= GETDATE())
    OR (e.E3 IS NULL OR e.E3 >= GETDATE())
    OR (e.E4 IS NULL OR e.E4 >= GETDATE());

3. 获取每个P1-P2组合的最新合作 #

若同一P1-P2存在多条合作记录，用窗口函数按起始日期倒序取最新一条：

WITH RankedPartnerships AS (
    SELECT
        s.P1,
        s.P2,
        s.St1 AS SdateSt1,
        e.E1 AS EdateSt1,
        s.St2 AS SdateSt2,
        e.E2 AS EdateSt2,
        s.St3 AS SdateSt3,
        e.E3 AS EdateSt3,
        s.St4 AS SdateSt4,
        e.E4 AS EdateSt4,
        ROW_NUMBER() OVER (PARTITION BY s.P1, s.P2 ORDER BY 
            COALESCE(s.St1, s.St2, s.St3, s.St4) DESC) AS rn
    FROM #tblStart s
    INNER JOIN #tblEnd e ON s.P1 = e.P1 AND s.P2 = e.P2
)
INSERT INTO #tblResult (P1, P2, SdateSt1, EdateSt1, SdateSt2, EdateSt2, SdateSt3, EdateSt3, SdateSt4, EdateSt4)
SELECT P1, P2, SdateSt1, EdateSt1, SdateSt2, EdateSt2, SdateSt3, EdateSt3, SdateSt4, EdateSt4
FROM RankedPartnerships
WHERE rn = 1;

验证结果 #

执行插入后，查询结果表确认数据：

SELECT * FROM #tblResult;

针对你的示例数据，基础关联版本会返回全部6条P1-P2组合的完整起止日期，完全匹配预期输出格式。

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