多表关联SQL查询：按日期获取最新支付类型记录求助

需求说明 #

需关联4张数据表（核心关联表为T16_RecurringTransactionHeaders、T17_RecurringDonations、A10_AccountPledges），从T16表中提取每个PledgeId对应的最新使用的PaymentType。表间关联规则：

• T16通过HeaderId字段关联T17_RecurringDonations
• T17通过PledgeId字段关联A10_AccountPledges

当前已有的SQL能获取各支付类型对应的最新LastUsedDate，但无法直接得到该日期对应的PaymentType，测试用例为A10.PledgeId = 398353。

原有SQL代码 #

SELECT DISTINCT
    A10.RecordId
    ,A10.AccountNumber
    ,A01.FamilyId
    ,a01.FamilyMemberType
    ,A10.PledgeCode  --Child Number
    ,A10.OriginalPledgeId
    ,A10.PledgeId
    ,A01.FirstName
    ,A01.LastName
    ,A10.PledgeStatus
    ,A10.AmountPerGift
    ,A10.PledgeFrequency
    ,t16.PaymentType
FROM 
    A10_AccountPledges A10
LEFT JOIN
    A01_AccountMaster A01 ON a01.AccountNumber = a10.AccountNumber
LEFT JOIN
    T17_RecurringDonations T17 ON T17.PledgeId = A10.PledgeId
LEFT JOIN
    T16_RecurringTransactionHeaders T16 ON T16.HeaderId = T17.HeaderId
INNER JOIN
    (SELECT 
         T17.pledgeID
         ,MAX(T16.LastUsedDate) as lastdate
     FROM 
         T17_RecurringDonations T17
     LEFT JOIN
         T16_RecurringTransactionHeaders T16 ON T16.HeaderId = T17.HeaderId
     GROUP BY
         T17.pledgeID) pm ON pm.PledgeId = A10.PledgeId --and pm.lastdate = T16.LastUsedDate
WHERE 
    A01.[Status] = 'A'
    AND a10.PledgeId = 398353 --test case

修正方案 #

方案1：利用ROW_NUMBER()函数（推荐） #

通过窗口函数按PledgeId分组，按LastUsedDate倒序排序，直接取每组最新的记录：

SELECT DISTINCT
    A10.RecordId
    ,A10.AccountNumber
    ,A01.FamilyId
    ,a01.FamilyMemberType
    ,A10.PledgeCode  --Child Number
    ,A10.OriginalPledgeId
    ,A10.PledgeId
    ,A01.FirstName
    ,A01.LastName
    ,A10.PledgeStatus
    ,A10.AmountPerGift
    ,A10.PledgeFrequency
    ,t16_latest.PaymentType
FROM 
    A10_AccountPledges A10
LEFT JOIN
    A01_AccountMaster A01 ON a01.AccountNumber = a10.AccountNumber
LEFT JOIN
    (SELECT 
         T17.PledgeId
         ,T16.PaymentType
         ,ROW_NUMBER() OVER (PARTITION BY T17.PledgeId ORDER BY T16.LastUsedDate DESC) AS rn
     FROM 
         T17_RecurringDonations T17
     LEFT JOIN
         T16_RecurringTransactionHeaders T16 ON T16.HeaderId = T17.HeaderId
    ) t16_latest ON t16_latest.PledgeId = A10.PledgeId AND t16_latest.rn = 1
WHERE 
    A01.[Status] = 'A'
    AND a10.PledgeId = 398353 --test case

• 若需保留无支付记录的Pledge（PaymentType为NULL），直接使用LEFT JOIN即可；若只需要有有效支付记录的，可将LEFT JOIN改为INNER JOIN，并在子查询中添加WHERE T16.LastUsedDate IS NOT NULL过滤无效记录。

方案2：修复原有SQL逻辑 #

原有代码中已注释掉关联条件and pm.lastdate = T16.LastUsedDate，取消注释即可关联到最新日期对应的PaymentType：

SELECT DISTINCT
    A10.RecordId
    ,A10.AccountNumber
    ,A01.FamilyId
    ,a01.FamilyMemberType
    ,A10.PledgeCode  --Child Number
    ,A10.OriginalPledgeId
    ,A10.PledgeId
    ,A01.FirstName
    ,A01.LastName
    ,A10.PledgeStatus
    ,A10.AmountPerGift
    ,A10.PledgeFrequency
    ,t16.PaymentType
FROM 
    A10_AccountPledges A10
LEFT JOIN
    A01_AccountMaster A01 ON a01.AccountNumber = a10.AccountNumber
LEFT JOIN
    T17_RecurringDonations T17 ON T17.PledgeId = A10.PledgeId
LEFT JOIN
    T16_RecurringTransactionHeaders T16 ON T16.HeaderId = T17.HeaderId
INNER JOIN
    (SELECT 
         T17.pledgeID
         ,MAX(T16.LastUsedDate) as lastdate
     FROM 
         T17_RecurringDonations T17
     LEFT JOIN
         T16_RecurringTransactionHeaders T16 ON T16.HeaderId = T17.HeaderId
     GROUP BY
         T17.pledgeID) pm ON pm.PledgeId = A10.PledgeId AND pm.lastdate = T16.LastUsedDate
WHERE 
    A01.[Status] = 'A'
    AND a10.PledgeId = 398353 --test case

• 注意：若同一PledgeId在同一LastUsedDate存在多个不同的PaymentType，此方案会返回多条记录，需根据实际业务场景处理（如加额外过滤条件或使用聚合函数）。

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