如何实现单推荐号对应双活动日期列且无重复行

需求说明 #

从CustomerHistory表中，为每个推荐号（Reference）提取Activity1和Activity2两个活动的日期，要求每个Reference对应一行记录，两个活动日期分属独立列，无日期则留空。当前执行的SQL查询在两个活动均有日期时会产生重复行。

当前SQL查询 #

SELECT 
    r.Reference, 
    CASE
        WHEN ch.NoteAction = 'Activity2' 
            THEN ch.zu_creationTimeStamp_um
    END AS 'Action_Activity2',
    CASE
        WHEN ch.NoteAction = 'Activity1' 
            THEN ch.zu_creationTimeStamp_um
    END AS 'Action_Activity1', 
    -- ch.zu_creationTimeStamp_um,
    ROW_NUMBER() OVER(PARTITION BY ch.id_Contact, ch.NoteAction ORDER BY ch.zu_creationTimeStamp_um DESC) AS 'row_num' 
FROM 
    hug2.ReferralUpdates r 
LEFT JOIN 
    hug2.CustomerHistory ch ON r.Reference = ch.id_Contact 
WHERE 
    ch.NoteAction = 'Activity2'
    OR ch.NoteAction = 'Activity1'

当前查询结果 #

期望结果 #

修正方案 #

方案1：聚合函数+CASE语句（通用所有SQL数据库） #

先筛选出每个推荐号每个活动的最新记录，再通过分组聚合将同一推荐号的两个活动日期合并到一行：

WITH LatestActivities AS (
    SELECT 
        ch.id_Contact,
        ch.NoteAction,
        ch.zu_creationTimeStamp_um,
        ROW_NUMBER() OVER(PARTITION BY ch.id_Contact, ch.NoteAction ORDER BY ch.zu_creationTimeStamp_um DESC) AS row_num
    FROM hug2.CustomerHistory ch
    WHERE ch.NoteAction IN ('Activity1', 'Activity2')
)
SELECT 
    r.Reference,
    MAX(CASE WHEN la.NoteAction = 'Activity1' THEN la.zu_creationTimeStamp_um END) AS Activity1,
    MAX(CASE WHEN la.NoteAction = 'Activity2' THEN la.zu_creationTimeStamp_um END) AS Activity2,
    1 AS row_num
FROM hug2.ReferralUpdates r
LEFT JOIN LatestActivities la ON r.Reference = la.id_Contact AND la.row_num = 1
GROUP BY r.Reference
ORDER BY r.Reference;

方案2：使用PIVOT（适用于支持该语法的数据库，如SQL Server） #

通过PIVOT将活动类型的行转换为列，实现一行展示：

WITH LatestActivities AS (
    SELECT 
        ch.id_Contact,
        ch.NoteAction,
        ch.zu_creationTimeStamp_um,
        ROW_NUMBER() OVER(PARTITION BY ch.id_Contact, ch.NoteAction ORDER BY ch.zu_creationTimeStamp_um DESC) AS row_num
    FROM hug2.CustomerHistory ch
    WHERE ch.NoteAction IN ('Activity1', 'Activity2')
)
SELECT 
    r.Reference,
    ISNULL(Activity1, '') AS Activity1,
    ISNULL(Activity2, '') AS Activity2,
    1 AS row_num
FROM hug2.ReferralUpdates r
LEFT JOIN (
    SELECT id_Contact, Activity1, Activity2
    FROM LatestActivities
    WHERE row_num = 1
    PIVOT (
        MAX(zu_creationTimeStamp_um)
        FOR NoteAction IN ([Activity1], [Activity2])
    ) AS PivotTable
) p ON r.Reference = p.id_Contact
ORDER BY r.Reference;

说明 #

• 两种方案都先通过ROW_NUMBER()筛选出每个推荐号对应每个活动的最新记录（row_num=1），避免同一活动有多条历史记录干扰。
• 方案1兼容性更强，几乎所有SQL数据库都支持；方案2写法更简洁，但仅适用于支持PIVOT语法的数据库。

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