如何编写MySQL查询将用户表与运动关联表结果合并为单行

嘿，我来帮你搞定这个把用户信息和关联运动合并成单行的需求！根据你给出的表结构和查询需求，我整理了几种可行的方案，你可以根据实际场景选择：

方案1：固定数量运动的行转列（最直接） #

如果这个用户关联的运动数量是固定的（比如最多2个），可以用条件聚合的方式直接生成sport1、sport2这类字段。假设tb_sports表的主键是sport_id，对应tb_user_sports的user_sport_sport，且运动名称字段是sport_name：

SELECT
    u.id,
    u.username,
    -- 取第1个运动
    MAX(CASE WHEN rn = 1 THEN s.sport_name END) AS sport1,
    -- 取第2个运动
    MAX(CASE WHEN rn = 2 THEN s.sport_name END) AS sport2
    -- 可以继续加更多CASE WHEN对应sport3、sport4...
FROM
    users u
LEFT JOIN (
    -- 给每个用户的运动按顺序编号
    SELECT
        user_sport_user,
        user_sport_sport,
        ROW_NUMBER() OVER (PARTITION BY user_sport_user ORDER BY user_sport_sport) AS rn
    FROM tb_user_sports
) us ON u.id = us.user_sport_user
LEFT JOIN tb_sports s ON us.user_sport_sport = s.sport_id
WHERE
    u.id = 71
GROUP BY
    u.id, u.username;

原理说明： #

1. 子查询里用ROW_NUMBER()给每个用户的运动条目按顺序编号（rn）；
2. 外层用CASE WHEN结合MAX，把不同编号的运动对应到单独的字段；
3. LEFT JOIN保证即使用户没有关联运动，也能返回用户基础信息。

方案2：动态生成运动字段（适配任意数量运动） #

如果用户关联的运动数量不固定，想要自动生成sport1、sport2、sport3...这类字段，可以用动态SQL实现：

-- 先设置目标用户ID
SET @user_id = 71;
SET @cols = NULL;

-- 自动生成需要的列定义（比如MAX(CASE WHEN rn=1 THEN ...) AS sport1）
SELECT GROUP_CONCAT(DISTINCT
    CONCAT('MAX(CASE WHEN rn = ', rn, ' THEN s.sport_name END) AS sport', rn)
) INTO @cols
FROM (
    SELECT ROW_NUMBER() OVER (PARTITION BY user_sport_user ORDER BY user_sport_sport) AS rn
    FROM tb_user_sports
    WHERE user_sport_user = @user_id
) t;

-- 拼接完整的查询语句
SET @query = CONCAT('
SELECT
    u.id,
    u.username,
    ', @cols, '
FROM
    users u
LEFT JOIN (
    SELECT
        user_sport_user,
        user_sport_sport,
        ROW_NUMBER() OVER (PARTITION BY user_sport_user ORDER BY user_sport_sport) AS rn
    FROM tb_user_sports
    WHERE user_sport_user = ', @user_id, '
) us ON u.id = us.user_sport_user
LEFT JOIN tb_sports s ON us.user_sport_sport = s.sport_id
WHERE
    u.id = ', @user_id, '
GROUP BY
    u.id, u.username
');

-- 执行动态生成的查询
PREPARE stmt FROM @query;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

原理说明： #

1. 先查询目标用户的运动数量，自动生成对应的列定义；
2. 拼接成完整的SQL语句后执行，这样不管用户有多少个关联运动，都会生成对应的sportN字段。

方案3：合并为单个运动字段（最简便） #

如果不需要拆分成单独的sport1、sport2，只是想把所有运动合并成一个字段，用GROUP_CONCAT最省事：

SELECT
    u.id,
    u.username,
    GROUP_CONCAT(s.sport_name SEPARATOR ', ') AS sports
FROM
    users u
LEFT JOIN tb_user_sports us ON u.id = us.user_sport_user
LEFT JOIN tb_sports s ON us.user_sport_sport = s.sport_id
WHERE
    u.id = 71
GROUP BY
    u.id, u.username;

这个查询会返回类似id=71, username="mike2", sports="Basketball, Soccer"的结果，适合不需要单独字段的场景。

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