基于重复ride_ID将person_ID多行值转换为多列（MySQL）

静态列转换（已知最大列数） #

如果能确定每个ride_ID对应的person_ID最大数量，可直接使用以下SQL实现行转列：

SELECT
    ride_ID,
    MAX(CASE WHEN rn = 1 THEN person_ID END) AS person_ID1,
    MAX(CASE WHEN rn = 2 THEN person_ID END) AS person_ID2,
    MAX(CASE WHEN rn = 3 THEN person_ID END) AS person_ID3,
    MAX(CASE WHEN rn = 4 THEN person_ID END) AS person_ID4,
    MAX(CASE WHEN rn = 5 THEN person_ID END) AS person_ID5
FROM (
    -- 给每个ride_ID下的person_ID分配序号
    SELECT
        ride_ID,
        person_ID,
        ROW_NUMBER() OVER (PARTITION BY ride_ID ORDER BY person_ID) AS rn
    FROM ride
) t
GROUP BY ride_ID;

如果需要保留person_ID在原表中的出现顺序（而非按person_ID排序），可使用会话变量替代窗口函数：

SELECT
    ride_ID,
    MAX(CASE WHEN rn = 1 THEN person_ID END) AS person_ID1,
    MAX(CASE WHEN rn = 2 THEN person_ID END) AS person_ID2,
    MAX(CASE WHEN rn = 3 THEN person_ID END) AS person_ID3,
    MAX(CASE WHEN rn = 4 THEN person_ID END) AS person_ID4,
    MAX(CASE WHEN rn = 5 THEN person_ID END) AS person_ID5
FROM (
    SELECT
        ride_ID,
        person_ID,
        @rn := IF(@prev_ride = ride_ID, @rn + 1, 1) AS rn,
        @prev_ride := ride_ID
    FROM ride, (SELECT @prev_ride := '', @rn := 0) vars
    -- 若表有自增主键/插入时间字段，建议替换为该字段排序以确保顺序稳定
    ORDER BY ride_ID
) t
GROUP BY ride_ID;

动态列转换（未知最大列数） #

如果ride_ID对应的person_ID数量不固定，可通过存储过程生成动态SQL实现：

-- 统计每个ride_ID最多包含的person_ID数量
SELECT MAX(rn) INTO @max_cols FROM (
    SELECT COUNT(*) AS rn FROM ride GROUP BY ride_ID
) t;

-- 拼接动态SQL语句
SET @sql = 'SELECT ride_ID';
SET @i = 1;
WHILE @i <= @max_cols DO
    SET @sql = CONCAT(@sql, ', MAX(CASE WHEN rn = ', @i, ' THEN person_ID END) AS person_ID', @i);
    SET @i = @i + 1;
END WHILE;
SET @sql = CONCAT(@sql, ' FROM (SELECT ride_ID, person_ID, ROW_NUMBER() OVER (PARTITION BY ride_ID ORDER BY person_ID) AS rn FROM ride) t GROUP BY ride_ID');

-- 执行动态SQL
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

性能优化建议 #

针对大型表，建议在ride_ID字段上建立索引，可大幅提升分组和窗口函数的计算效率：

CREATE INDEX idx_ride_ride_id ON ride(ride_ID);

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