如何在MySQL中将同一房间的多行人员数据合并为单行多列展示

解决方法 #

方法一：MySQL 8.0+ 支持窗口函数版本（推荐） #

先给每个房间内的入住人员分配序号，再通过条件聚合实现行转列：

WITH guest_ranked AS (
    SELECT 
        房间ID,
        first_name,
        last_name,
        dob,
        -- 按房间分组给客人分配序号，排序规则可按需调整
        ROW_NUMBER() OVER (PARTITION BY 房间ID ORDER BY 个人ID) AS guest_num
    FROM 你的表名
)
SELECT 
    房间ID,
    MAX(CASE WHEN guest_num = 1 THEN first_name END) AS Guest1_First,
    MAX(CASE WHEN guest_num = 1 THEN last_name END) AS Guest1_Last,
    MAX(CASE WHEN guest_num = 1 THEN dob END) AS Guest1_dob,
    MAX(CASE WHEN guest_num = 2 THEN first_name END) AS Guest2_First,
    MAX(CASE WHEN guest_num = 2 THEN last_name END) AS Guest2_Last,
    MAX(CASE WHEN guest_num = 2 THEN dob END) AS Guest2_dob,
    -- 按相同格式补充到guest_num=6即可
    MAX(CASE WHEN guest_num = 6 THEN first_name END) AS Guest6_First,
    MAX(CASE WHEN guest_num = 6 THEN last_name END) AS Guest6_Last,
    MAX(CASE WHEN guest_num = 6 THEN dob END) AS Guest6_dob
FROM guest_ranked
GROUP BY 房间ID;

方法二：MySQL 5.x 不支持窗口函数版本 #

通过用户变量实现序号分配：

SELECT 
    房间ID,
    MAX(CASE WHEN guest_num = 1 THEN first_name END) AS Guest1_First,
    MAX(CASE WHEN guest_num = 1 THEN last_name END) AS Guest1_Last,
    MAX(CASE WHEN guest_num = 1 THEN dob END) AS Guest1_dob,
    MAX(CASE WHEN guest_num = 2 THEN first_name END) AS Guest2_First,
    MAX(CASE WHEN guest_num = 2 THEN last_name END) AS Guest2_Last,
    MAX(CASE WHEN guest_num = 2 THEN dob END) AS Guest2_dob,
    -- 按相同格式补充到guest_num=6即可
    MAX(CASE WHEN guest_num = 6 THEN first_name END) AS Guest6_First,
    MAX(CASE WHEN guest_num = 6 THEN last_name END) AS Guest6_Last,
    MAX(CASE WHEN guest_num = 6 THEN dob END) AS Guest6_dob
FROM (
    SELECT 
        t.*,
        @num := IF(@room_id = t.房间ID, @num + 1, 1) AS guest_num,
        @room_id := t.房间ID
    FROM 你的表名 t,
    (SELECT @num := 0, @room_id := NULL) AS init
    ORDER BY t.房间ID, t.个人ID
) AS guest_ranked
GROUP BY 房间ID;

报错原因说明 #

你遇到的1055报错是因为MySQL开启了only_full_group_by模式，该模式要求GROUP BY查询中，SELECT列表里的非聚合字段必须全部出现在GROUP BY子句中，或者使用聚合函数包裹。上述方案所有非GROUP BY的字段都用了MAX聚合函数，完全符合该模式的要求，不会触发报错。

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