多表关联GROUP BY后排序报错，如何正确去重并排序？

核心问题原因 #

1. 分组后字段引用违规：users和users_game_level是一对多关系，GROUP BY u.id后，每个分组对应多条子表记录。ORDER BY里直接用l.level、l.game_code时，数据库无法确定取分组中哪一条子表记录的字段值，违反SQL分组规则——非聚合列必须出现在GROUP BY子句中，或通过聚合函数处理。
2. 语法错误：原CASE语句的第二个WHEN条件漏写了AND，应为WHEN l.level = 'MEDIUM' AND l.game_code = 'race' THEN 2。
3. u.last_online_at报错问题：如果u.id是users表主键，部分数据库（如PostgreSQL）支持通过主键推导其他字段，但如果数据库不支持该特性，或u.id不是主键，就会要求u.last_online_at也加入GROUP BY，但这样会导致分组粒度变细，返回重复用户ID。

解决方案 #

根据你的排序需求（按子表条件优先级排序用户），提供两种常用方案：

方案1：取用户优先级最高的子表记录排序

适用于需要基于用户某一条特定子表记录（符合最高优先级条件的记录）排序的场景：

SELECT u.id
FROM users u
JOIN (
    SELECT 
        user_id,
        CASE
            WHEN level = 'BEGINNER' AND game_code = 'arcade' THEN 1
            WHEN level = 'MEDIUM' AND game_code = 'race' THEN 2
            WHEN level = 'PROF' THEN 3
            ELSE 4  -- 为无匹配条件的记录设置默认优先级
        END AS sort_priority,
        -- 按优先级排序，给每个用户的子表记录编号，取优先级最高的第一条
        ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY 
            CASE
                WHEN level = 'BEGINNER' AND game_code = 'arcade' THEN 1
                WHEN level = 'MEDIUM' AND game_code = 'race' THEN 2
                WHEN level = 'PROF' THEN 3
                ELSE 4
            END ASC
        ) AS rn
    FROM users_game_level
) l ON l.user_id = u.id AND l.rn = 1
ORDER BY l.sort_priority ASC, u.last_online_at DESC, u.id DESC
LIMIT 100;

方案2：按用户是否存在符合条件的子表记录排序

适用于只需判断用户是否拥有某类子表记录，按条件优先级排序的场景：

SELECT u.id
FROM users u
LEFT JOIN users_game_level l ON l.user_id = u.id
GROUP BY u.id, u.last_online_at
ORDER BY
    -- 用聚合函数判断用户是否存在对应条件的子表记录，按优先级赋值
    CASE
        WHEN MAX(CASE WHEN l.level = 'BEGINNER' AND l.game_code = 'arcade' THEN 1 ELSE 0 END) = 1 THEN 1
        WHEN MAX(CASE WHEN l.level = 'MEDIUM' AND l.game_code = 'race' THEN 1 ELSE 0 END) = 1 THEN 2
        WHEN MAX(CASE WHEN l.level = 'PROF' THEN 1 ELSE 0 END) = 1 THEN 3
        ELSE 4
    END ASC,
    u.last_online_at DESC,
    u.id DESC
LIMIT 100;

为什么加字段到GROUP BY会重复数据 #

当把l.level、l.game_code加入GROUP BY后，分组粒度从「单个用户」变成「用户+子表记录的level+game_code组合」，同一个用户的不同子表记录会被拆分成不同分组，最终返回多条相同的用户ID，导致重复数据。

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