如何在SQL中基于指定日期获取过去6个月的PaymentProfile

需求：为每笔付款生成PaymentProfile列，展示该付款到期日过去6个月内的付款状态（P=已付，U=未付，?=待处理），按到期日期从早到晚拼接成字符串。

输入数据示例 #

期望输出示例 #

解决方案（分SQL方言） #

1. MySQL 8.0+（支持窗口函数 STRING_AGG） #

SELECT
    t1.PaidHistory,
    t1.PersonId,
    t1.PaymentId,
    t1.PaymentDueDt,
    STRING_AGG(t2.PaidHistory, '') WITHIN GROUP (ORDER BY t2.PaymentDueDt) AS PaymentProfile
FROM
    YourTable t1
JOIN
    YourTable t2 ON t1.PersonId = t2.PersonId
        AND t2.PaymentDueDt >= DATE_SUB(t1.PaymentDueDt, INTERVAL 6 MONTH)
        AND t2.PaymentDueDt <= t1.PaymentDueDt
GROUP BY
    t1.PaidHistory, t1.PersonId, t1.PaymentId, t1.PaymentDueDt
ORDER BY
    t1.PaymentDueDt;

2. PostgreSQL #

PostgreSQL用 STRING_AGG 函数，逻辑和MySQL一致：

SELECT
    t1.PaidHistory,
    t1.PersonId,
    t1.PaymentId,
    t1.PaymentDueDt,
    STRING_AGG(t2.PaidHistory, '' ORDER BY t2.PaymentDueDt) AS PaymentProfile
FROM
    your_table t1
JOIN
    your_table t2 ON t1.PersonId = t2.PersonId
        AND t2.PaymentDueDt >= t1.PaymentDueDt - INTERVAL '6 months'
        AND t2.PaymentDueDt <= t1.PaymentDueDt
GROUP BY
    t1.PaidHistory, t1.PersonId, t1.PaymentId, t1.PaymentDueDt
ORDER BY
    t1.PaymentDueDt;

3. SQL Server #

方法1：SQL Server 2017+（支持 STRING_AGG）

SELECT
    t1.PaidHistory,
    t1.PersonId,
    t1.PaymentId,
    t1.PaymentDueDt,
    STRING_AGG(t2.PaidHistory, '') WITHIN GROUP (ORDER BY t2.PaymentDueDt) AS PaymentProfile
FROM
    YourTable t1
JOIN
    YourTable t2 ON t1.PersonId = t2.PersonId
        AND t2.PaymentDueDt >= DATEADD(MONTH, -6, t1.PaymentDueDt)
        AND t2.PaymentDueDt <= t1.PaymentDueDt
GROUP BY
    t1.PaidHistory, t1.PersonId, t1.PaymentId, t1.PaymentDueDt
ORDER BY
    t1.PaymentDueDt;

方法2：兼容旧版SQL Server（无 STRING_AGG）

SELECT
    t1.PaidHistory,
    t1.PersonId,
    t1.PaymentId,
    t1.PaymentDueDt,
    (
        SELECT PaidHistory + ''
        FROM YourTable t2
        WHERE t2.PersonId = t1.PersonId
            AND t2.PaymentDueDt >= DATEADD(MONTH, -6, t1.PaymentDueDt)
            AND t2.PaymentDueDt <= t1.PaymentDueDt
        ORDER BY t2.PaymentDueDt
        FOR XML PATH(''), TYPE
    ).value('.', 'NVARCHAR(MAX)') AS PaymentProfile
FROM
    YourTable t1
ORDER BY
    t1.PaymentDueDt;

4. 旧版MySQL（无 STRING_AGG，用 GROUP_CONCAT） #

SELECT
    t1.PaidHistory,
    t1.PersonId,
    t1.PaymentId,
    t1.PaymentDueDt,
    GROUP_CONCAT(t2.PaidHistory ORDER BY t2.PaymentDueDt SEPARATOR '') AS PaymentProfile
FROM
    YourTable t1
JOIN
    YourTable t2 ON t1.PersonId = t2.PersonId
        AND t2.PaymentDueDt >= DATE_SUB(t1.PaymentDueDt, INTERVAL 6 MONTH)
        AND t2.PaymentDueDt <= t1.PaymentDueDt
GROUP BY
    t1.PaidHistory, t1.PersonId, t1.PaymentId, t1.PaymentDueDt
ORDER BY
    t1.PaymentDueDt;

逻辑说明 #

1. 自关联表：通过PersonId关联同一用户的付款记录，筛选出当前记录到期日之前6个月内的所有付款记录。
2. 排序聚合：将筛选出的付款状态按到期日期从早到晚排序，然后拼接成字符串，得到PaymentProfile。
3. 分组返回：按当前记录的所有字段分组，返回每笔付款对应的6个月付款档案。

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