MySQL/MariaDB多表关联多行转列查询实现求助

要实现将TableB的行转为对应列，同时关联TableA的数据，核心思路是给每个id下的TableB记录生成序号，再通过聚合函数把不同序号的name/phone映射到对应列，最后关联TableA保留所有用户数据。

完整SQL代码 #

SELECT
    a.id,
    a.username,
    MAX(CASE WHEN rn = 1 THEN b.name END) AS name1,
    MAX(CASE WHEN rn = 1 THEN b.phone END) AS phone1,
    MAX(CASE WHEN rn = 2 THEN b.name END) AS name2,
    MAX(CASE WHEN rn = 2 THEN b.phone END) AS phone2,
    MAX(CASE WHEN rn = 3 THEN b.name END) AS name3,
    MAX(CASE WHEN rn = 3 THEN b.phone END) AS phone3
FROM
    TableA a
LEFT JOIN (
    -- 给每个id下的记录生成序号，用于区分不同行
    SELECT
        id,
        name,
        phone,
        ROW_NUMBER() OVER (PARTITION BY id ORDER BY name) AS rn
    FROM TableB
) b ON a.id = b.id
GROUP BY
    a.id, a.username
ORDER BY
    a.id;

代码说明 #

1. 子查询生成序号：用ROW_NUMBER() OVER (PARTITION BY id ORDER BY name)给每个id分组内的记录编序号（这里按name排序，也可以换成phone或其他字段），每个id下第一条记录rn=1，第二条rn=2，以此类推。
2. CASE WHEN + 聚合函数转列：通过MAX(CASE WHEN rn = N THEN ... END)提取对应序号的name和phone，聚合函数MAX用来过滤NULL值，确保每个id只保留一行数据。
3. LEFT JOIN关联表：用LEFT JOIN保证TableA里的所有id都会被保留，哪怕TableB里没有对应数据（对应字段显示为NULL）。
4. 分组与排序：按TableA的id和username分组，最后按id排序保证输出顺序和示例一致。

输出效果 #

执行后会得到和你期望一致的结果：

如果需要显示空字符串而非NULL，可把MAX(CASE...)改成COALESCE(MAX(CASE...), '')。

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