MySQL一对多关联查询：将结果映射为多列的实现问题

看起来你已经尝试了基础的JOIN和GROUP BY，但因为没对每个用户的关联地点做区分，所以只能拿到第一条记录。要实现你想要的横向多列展示，我们可以用窗口函数给地点编号，再配合条件聚合来把多行数据转成多列。

解决方案代码 #

这里以你的表结构为例，写出可直接运行的SQL：

SELECT
    u.id AS user_id,
    u.user,
    MAX(CASE WHEN rn = 1 THEN l.location END) AS location_1,
    MAX(CASE WHEN rn = 2 THEN l.location END) AS location_2,
    MAX(CASE WHEN rn = 3 THEN l.location END) AS location_3
FROM
    users u
INNER JOIN
    user_locations ul ON u.id = ul.user_id
INNER JOIN
    locations l ON ul.location_id = l.id
-- 子查询：给每个用户的关联地点分配序号
INNER JOIN (
    SELECT
        user_id,
        location_id,
        -- 按用户分组，给每个用户的地点排号；ORDER BY可按需调整顺序
        ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY location_id) AS rn
    FROM user_locations
) rn_ul 
    ON ul.user_id = rn_ul.user_id 
    AND ul.location_id = rn_ul.location_id
GROUP BY
    u.id, u.user
ORDER BY
    u.id;

代码解释 #

1. 窗口函数编号：子查询里的ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY location_id)会把每个用户的关联地点单独分组，然后给每组里的地点分配一个唯一序号（1、2、3...）。你可以修改ORDER BY后面的字段来调整地点的排列顺序，比如想按地点名称排序就改成ORDER BY l.location。
2. 条件聚合：外层用MAX(CASE ...)来筛选对应序号的地点，把它们放到location_1、location_2等列里。因为每个用户每个序号只会有一个地点，MAX函数能精准取出对应的值，没有对应序号的位置会自动填充为NULL。
3. GROUP BY：最后按用户ID和用户名分组，确保每个用户只显示一行结果。

补充说明 #

如果你的用户关联地点数量不固定（可能超过3个），静态的CASE语句就不够用了，这时候需要根据数据库类型写动态SQL（比如MySQL用存储过程、PostgreSQL用动态语句生成）。但从你的示例来看，最多3个地点，上面的静态方案完全适用。

内容的提问来源于stack exchange，提问作者Erwin van Hoof