MySQL动态查询：展示当前用户及好友的科目分数

实现思路 #

要实现需求，核心是先获取当前用户及其所有好友的ID集合，再基于这个集合动态拼接SQL，将每个用户的math、science分数作为独立列返回。

具体实现 #

1. 获取目标用户ID集合 #

先通过查询拿到当前用户和其所有好友的ID：

SELECT user_id FROM (
    -- 包含当前用户自身
    SELECT %CURRENT_USER_ID% AS user_id
    UNION
    -- 包含当前用户的所有好友
    SELECT friend_id FROM friends WHERE user_id = %CURRENT_USER_ID%
) AS target_users;

2. 用存储过程实现动态SQL拼接 #

MySQL无法直接在静态SQL中生成动态列，借助存储过程可以完成这个逻辑：

DELIMITER //

CREATE PROCEDURE GetUserAndFriendsScores(IN current_user_id INT)
BEGIN
    DECLARE dynamic_sql VARCHAR(4000);
    DECLARE user_ids VARCHAR(1000);
    
    -- 把目标用户ID用逗号拼接成字符串
    SELECT GROUP_CONCAT(DISTINCT user_id) INTO user_ids FROM (
        SELECT current_user_id AS user_id
        UNION
        SELECT friend_id FROM friends WHERE user_id = current_user_id
    ) AS target_users;
    
    -- 动态拼接每个用户的分数列，命名格式为user_{ID}_math、user_{ID}_science
    SET dynamic_sql = CONCAT(
        'SELECT ',
        GROUP_CONCAT(
            CONCAT(
                'MAX(CASE WHEN s.user_id = ', user_id, ' THEN s.math END) AS user_', user_id, '_math, ',
                'MAX(CASE WHEN s.user_id = ', user_id, ' THEN s.science END) AS user_', user_id, '_science'
            )
            SEPARATOR ', '
        ),
        ' FROM score s WHERE s.user_id IN (', user_ids, ')'
    );
    
    -- 执行动态生成的SQL
    PREPARE stmt FROM dynamic_sql;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;
END //

DELIMITER ;

3. 调用存储过程 #

传入当前用户ID即可获取结果：

CALL GetUserAndFriendsScores(%CURRENT_USER_ID%);

示例效果 #

• 当%CURRENT_USER_ID%为1时，结果会生成列：user_1_math、user_1_science、user_53_math、user_53_science、user_59_math、user_59_science、user_114_math、user_114_science
• 当%CURRENT_USER_ID%为7时，结果列则为：user_7_math、user_7_science、user_9_math、user_9_science

注意事项 #

• 确保score表已转换为宽表，每行对应单个用户且包含math、science字段；
• 若用户或好友无分数记录，对应列会返回NULL；
• 若好友数量较多，需调整group_concat_max_len参数避免拼接长度溢出。

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