基于ID键的MySQL表转置需求：用GROUP BY生成新列

我来帮你搞定这个MySQL转置的需求！本质上就是把按ID分组的多行状态记录，转成一行展示所有状态对应的时间，咱们一步步来实现。

先明确场景与示例数据 #

假设你的表名叫status_tracking，结构和你给出的示例数据补全后是这样的：

CREATE TABLE status_tracking (
    ID INT,
    Dates DATETIME,
    State VARCHAR(50)
);

INSERT INTO status_tracking VALUES
(1, '2015-10-01 21:20:40', 'Arrived'),
(1, '2015-10-01 21:21:40', 'Pulling In'),
(1, '2015-10-01 21:31:40', 'Unloading'),
(1, '2015-10-01 21:50:48', 'Finished Unloading'),
(1, '2015-10-01 21:55:48', 'Pulled Out'),
(2, '2015-10-01 19:10:00', 'Arrived'),
(2, '2015-10-01 19:15:00', 'Pulling In');

静态转置：固定状态列的解决方案 #

MySQL没有内置的PIVOT函数，但咱们可以用GROUP BY配合CASE表达式手动实现转置。核心逻辑是按ID分组，给每个State单独生成一列，提取对应的时间值：

SELECT
    ID,
    MAX(CASE WHEN State = 'Arrived' THEN Dates END) AS Arrived_Time,
    MAX(CASE WHEN State = 'Pulling In' THEN Dates END) AS Pulling_In_Time,
    MAX(CASE WHEN State = 'Unloading' THEN Dates END) AS Unloading_Time,
    MAX(CASE WHEN State = 'Finished Unloading' THEN Dates END) AS Finished_Unloading_Time,
    MAX(CASE WHEN State = 'Pulled Out' THEN Dates END) AS Pulled_Out_Time
FROM status_tracking
GROUP BY ID;

为啥要用MAX()？ #

因为按ID分组后，每条记录对应一个状态，CASE表达式对不匹配的状态会返回NULL，用MAX()（或者MIN()也可以，因为每个ID的每个状态只会出现一次）能过滤掉NULL，只保留有效的时间值。

动态转置：适配新增状态的进阶方案 #

如果你的State值会动态新增，不想每次都手动修改SQL，可以用存储过程自动生成转置语句：

DELIMITER //
CREATE PROCEDURE DynamicStatusPivot()
BEGIN
    -- 自动拼接所有状态对应的列语句
    SET @cols = NULL;
    SELECT GROUP_CONCAT(DISTINCT CONCAT(
        'MAX(CASE WHEN State = ''', State, ''' THEN Dates END) AS ', REPLACE(State, ' ', '_'), '_Time'
    )) INTO @cols FROM status_tracking;

    -- 拼接完整的查询SQL
    SET @sql = CONCAT('SELECT ID, ', @cols, ' FROM status_tracking GROUP BY ID');

    -- 执行动态生成的SQL
    PREPARE stmt FROM @sql;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;
END //
DELIMITER ;

-- 调用存储过程即可得到动态转置结果
CALL DynamicStatusPivot();

最终效果示例 #

执行静态SQL后，你会得到这样的结构化结果：

这样就完美实现了基于ID的转置需求啦！

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