MySQL中如何将工单状态时间线转换为宽表格式？

你遇到的问题是因为CASE语句与聚合函数的搭配逻辑不够精准，导致部分状态对应的时间戳未被正确捕获。以下是可实现需求的具体解决方案：

基础实现方案（兼容所有MySQL版本） #

SELECT
    ticket_id AS `ticket id`,
    MAX(CASE WHEN status = 'created' THEN created END) AS created,
    MAX(CASE WHEN status = 'review' THEN created END) AS review,
    MAX(CASE WHEN status = 'completed' THEN created END) AS completed,
    -- 通过按时间倒序拼接状态，取第一个值作为最新状态
    SUBSTRING_INDEX(
        GROUP_CONCAT(status ORDER BY created DESC SEPARATOR ','),
        ',',
        1
    ) AS latest_status
FROM
    your_status_table -- 替换为你的实际表名
GROUP BY
    ticket_id;

问题原因分析 #

你之前的结果仅completed字段有值，大概率是以下原因：

• CASE语句中状态字符串与实际表中数据不匹配（比如大小写、空格差异）
• 未为每个状态单独编写CASE分支，导致聚合函数无法捕获对应时间戳

进阶方案（MySQL 8.0+，用窗口函数更直观） #

如果你的MySQL版本是8.0及以上，使用窗口函数ROW_NUMBER()可以更清晰地获取最新状态：

WITH ranked_tickets AS (
    SELECT
        ticket_id,
        status,
        created,
        -- 按工单分组，按时间倒序排名
        ROW_NUMBER() OVER (PARTITION BY ticket_id ORDER BY created DESC) AS rn
    FROM your_status_table
)
SELECT
    rt.ticket_id AS `ticket id`,
    MAX(CASE WHEN t.status = 'created' THEN t.created END) AS created,
    MAX(CASE WHEN t.status = 'review' THEN t.created END) AS review,
    MAX(CASE WHEN t.status = 'completed' THEN t.created END) AS completed,
    rt.status AS latest_status
FROM ranked_tickets rt
JOIN your_status_table t ON rt.ticket_id = t.ticket_id
WHERE rt.rn = 1 -- 取每个工单的最新状态记录
GROUP BY rt.ticket_id, rt.status;

注意事项 #

• 确保status字段的取值与CASE语句中的字符串完全一致，避免拼写、大小写或空格问题
• 如果created是字符串类型，需保证日期格式统一，否则会影响排序和聚合结果

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