SQL如何动态新增列 将客户多笔跟进记录合并为单行展示

实现思路 #

业务规则明确单客户单查询周期内跟进记录最多10条，不需要写复杂动态SQL，通过窗口函数给同客户的跟进记录按跟进时间排序编号，再用条件聚合做行转列即可，自动适配不同客户的跟进条数差异，条数不足的对应序号列默认返回空值，完全在SQL层完成计算，不需要依赖外部BI工具的透视功能。

完整代码示例（以SQL Server语法为例） #

WITH customer_latest_order AS (
    -- 如果CustomerOrders是订单明细粒度，先取每个客户的最新有效订单日期
    SELECT
        CustomerID,
        FirstName,
        LastName,
        MAX(OrderDate) AS LastOrderDate
    FROM CustomerOrders
    -- 按需补充过滤条件，排除作废、退款等无效订单
    GROUP BY CustomerID, FirstName, LastName
),
followup_ranked AS (
    -- 关联符合条件的客户与跟进记录，给跟进记录按时间排序打序号
    SELECT
        clo.FirstName,
        clo.LastName,
        clo.LastOrderDate,
        DATEDIFF(DAY, clo.LastOrderDate, GETDATE()) AS NumDaysSince,
        c.SalesStaffCommentDate,
        c.SalesComment,
        ROW_NUMBER() OVER (
            PARTITION BY clo.CustomerID
            ORDER BY c.SalesStaffCommentDate ASC -- 按跟进时间从早到晚排序
        ) AS rn
    FROM customer_latest_order clo
    LEFT JOIN Communications c
        ON clo.CustomerID = c.CustomerID
    WHERE DATEDIFF(DAY, clo.LastOrderDate, GETDATE()) > 4 -- 筛选超4天未下单客户
)
-- 条件聚合完成行转列
SELECT
    FirstName,
    LastName,
    LastOrderDate,
    NumDaysSince,
    MAX(CASE WHEN rn = 1 THEN SalesStaffCommentDate END) AS SalesStaffCommentDate1,
    MAX(CASE WHEN rn = 1 THEN SalesComment END) AS SalesComment1,
    MAX(CASE WHEN rn = 2 THEN SalesStaffCommentDate END) AS SalesStaffCommentDate2,
    MAX(CASE WHEN rn = 2 THEN SalesComment END) AS SalesComment2,
    MAX(CASE WHEN rn = 3 THEN SalesStaffCommentDate END) AS SalesStaffCommentDate3,
    MAX(CASE WHEN rn = 3 THEN SalesComment END) AS SalesComment3,
    MAX(CASE WHEN rn = 4 THEN SalesStaffCommentDate END) AS SalesStaffCommentDate4,
    MAX(CASE WHEN rn = 4 THEN SalesComment END) AS SalesComment4,
    MAX(CASE WHEN rn = 5 THEN SalesStaffCommentDate END) AS SalesStaffCommentDate5,
    MAX(CASE WHEN rn = 5 THEN SalesComment END) AS SalesComment5,
    MAX(CASE WHEN rn = 6 THEN SalesStaffCommentDate END) AS SalesStaffCommentDate6,
    MAX(CASE WHEN rn = 6 THEN SalesComment END) AS SalesComment6,
    MAX(CASE WHEN rn = 7 THEN SalesStaffCommentDate END) AS SalesStaffCommentDate7,
    MAX(CASE WHEN rn = 7 THEN SalesComment END) AS SalesComment7,
    MAX(CASE WHEN rn = 8 THEN SalesStaffCommentDate END) AS SalesStaffCommentDate8,
    MAX(CASE WHEN rn = 8 THEN SalesComment END) AS SalesComment8,
    MAX(CASE WHEN rn = 9 THEN SalesStaffCommentDate END) AS SalesStaffCommentDate9,
    MAX(CASE WHEN rn = 9 THEN SalesComment END) AS SalesComment9,
    MAX(CASE WHEN rn = 10 THEN SalesStaffCommentDate END) AS SalesStaffCommentDate10,
    MAX(CASE WHEN rn = 10 THEN SalesComment END) AS SalesComment10
FROM followup_ranked
GROUP BY FirstName, LastName, LastOrderDate, NumDaysSince

适配说明 #

• 样例中John Smith有3条跟进记录时，返回结果仅1行，SalesStaffCommentDate1~3、SalesComment1~3按跟进顺序填充值，序号4-10的对应列自动返回NULL，不存在冗余行
• 跨数据库适配只需要调整日期计算函数即可：
  • MySQL 8.0+：将DATEDIFF(DAY, clo.LastOrderDate, GETDATE())替换为DATEDIFF(CURDATE(), clo.LastOrderDate)，GETDATE()替换为CURDATE()
  • PostgreSQL：将日期差计算替换为(CURRENT_DATE - clo.LastOrderDate)
• 如果后续业务调整单客户跟进条数上限超过10条，只需要对应增加MAX(CASE...)的列即可，维护成本极低。

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