SQL Server 2012中忽略TYPE=0合约获取下一个有效合约日期

要搞定这个问题，核心就是让LEAD相关的关联逻辑只在**有效合约（TYPE≠0）**之间生效，同时还得保留原表的所有记录（包括TYPE=0的空合约）。因为SQL Server 2012还不支持LEAD的IGNORE NULLS参数，我给你两种适配版本的可行方案：

方案一：CTE筛选有效合约后关联（推荐） #

这是最直观且性能友好的方案，分三步处理：

1. 先筛选出所有有效合约，给每个房屋下的有效合约按入住时间排序编号；
2. 将每个有效合约和它的下一个编号的有效合约关联，获取后续合约的信息；
3. 最后把原表和处理后的有效合约数据左连接，保留所有原始记录。

完整SQL语句：

WITH ValidContracts AS (
    -- 筛选有效合约，按房屋分组并按入住时间排序编号
    SELECT 
        CONTRACTID,
        RENTALOBJECTID,
        VALIDFROM,
        VALIDTO,
        ROW_NUMBER() OVER (PARTITION BY RENTALOBJECTID ORDER BY VALIDFROM) AS ContractRN
    FROM PMCCONTRACT
    WHERE TYPE != 0
),
ValidContractsWithNext AS (
    -- 关联每个有效合约的下一个有效合约
    SELECT 
        vc_current.*,
        vc_next.CONTRACTID AS NextContractId,
        vc_next.VALIDFROM AS NextValidFrom,
        vc_next.VALIDTO AS NextValidTo
    FROM ValidContracts vc_current
    LEFT JOIN ValidContracts vc_next 
        ON vc_current.RENTALOBJECTID = vc_next.RENTALOBJECTID 
        AND vc_current.ContractRN + 1 = vc_next.ContractRN
)
-- 左连接原表，保留所有记录并填充正确的Next字段
SELECT 
    p.CONTRACTID,
    p.RENTALOBJECTID,
    p.TYPE,
    p.VALIDFROM,
    p.VALIDTO,
    vcn.NextContractId,
    vcn.NextValidFrom,
    vcn.NextValidTo,
    -- 可选：计算间隔天数，注意处理当前合约未到期（VALIDTO为NULL）的情况
    CASE 
        WHEN p.VALIDTO IS NOT NULL AND vcn.NextValidFrom IS NOT NULL 
        THEN DATEDIFF(day, p.VALIDTO, vcn.NextValidFrom)
        ELSE NULL
    END AS DaysBetweenContracts
FROM PMCCONTRACT p
LEFT JOIN ValidContractsWithNext vcn 
    ON p.CONTRACTID = vcn.CONTRACTID
ORDER BY p.RENTALOBJECTID, p.VALIDFROM;

方案二：子查询直接定位下一个有效合约 #

如果不想用CTE，也可以用关联子查询直接找到当前合约之后的第一个有效合约，不过这种写法在数据量大时性能会稍差：

SELECT 
    CONTRACTID,
    RENTALOBJECTID,
    TYPE,
    VALIDFROM,
    VALIDTO,
    -- 找到当前合约之后的第一个有效合约ID
    (SELECT TOP 1 CONTRACTID 
     FROM PMCCONTRACT p2 
     WHERE p2.RENTALOBJECTID = p1.RENTALOBJECTID 
       AND p2.TYPE != 0 
       AND p2.VALIDFROM > p1.VALIDTO) AS NextContractId,
    -- 同理获取后续的ValidFrom和ValidTo
    (SELECT TOP 1 VALIDFROM 
     FROM PMCCONTRACT p2 
     WHERE p2.RENTALOBJECTID = p1.RENTALOBJECTID 
       AND p2.TYPE != 0 
       AND p2.VALIDFROM > p1.VALIDTO) AS NextValidFrom,
    (SELECT TOP 1 VALIDTO 
     FROM PMCCONTRACT p2 
     WHERE p2.RENTALOBJECTID = p1.RENTALOBJECTID 
       AND p2.TYPE != 0 
       AND p2.VALIDFROM > p1.VALIDTO) AS NextValidTo
FROM PMCCONTRACT p1
ORDER BY RENTALOBJECTID, VALIDFROM;

结果验证 #

执行第一种方案后，你会得到和期望完全一致的结果：

• TYPE=3的有效合约HC001的NextContractId会指向HC003，DaysBetweenContracts会计算出从1/1/2017到8/2/2017的间隔天数；
• TYPE=0的空合约HC002的所有NextXXX字段都是NULL，不会干扰有效合约的关联逻辑。

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