MySQL多表关联查询：获取最高level的前3名用户及其详情

表结构与示例数据 #

myTab1（用户信息表，userId为主键） #

myTab2（交易记录表，无主键） #

需求 #

获取每个用户最高level的记录，同时关联展示myTab1中的name字段，最终按level降序取前3条，预期结果：

原错误查询语句 #

SELECT 
b.*, 
a.name 
FROM 
myTab1 AS b 
INNER JOIN myTab2 as a ON b.userId=a.userId 
ORDER BY level DESC 
limit 3

错误原因 #

1. 表别名逻辑混乱：将myTab1设为b、myTab2设为a，导致关联字段对应错误，且b.*会取出用户表的所有字段，不符合结果结构要求
2. 未筛选用户最高level记录：直接排序取前3仅会拿到单条最高level的交易记录，无法覆盖所有用户的最高level数据

修正后的查询语句 #

方法一：子查询筛选最高level（兼容所有MySQL版本） #

SELECT 
    t2.userId,
    t1.name,
    t2.level,
    t2.score
FROM 
    myTab1 t1
INNER JOIN 
    myTab2 t2 ON t1.userId = t2.userId
INNER JOIN 
    (SELECT userId, MAX(level) AS max_level FROM myTab2 GROUP BY userId) t3
    ON t2.userId = t3.userId AND t2.level = t3.max_level
ORDER BY 
    t2.level DESC
LIMIT 3;

方法二：窗口函数（MySQL 8.0+支持） #

SELECT 
    userId,
    name,
    level,
    score
FROM (
    SELECT 
        t2.userId,
        t1.name,
        t2.level,
        t2.score,
        ROW_NUMBER() OVER (PARTITION BY t2.userId ORDER BY t2.level DESC) AS rn
    FROM 
        myTab1 t1
    INNER JOIN 
        myTab2 t2 ON t1.userId = t2.userId
) temp
WHERE rn = 1
ORDER BY level DESC
LIMIT 3;

说明 #

• 方法一先通过子查询统计每个用户的最高level，再关联交易表获取对应score，最后关联用户表拿到name字段
• 方法二利用ROW_NUMBER()窗口函数按用户分组，每组内按level降序排序，取每组第一条（即最高level记录），再排序取前3
• 两种方法都能保证每个用户仅返回其最高level的那条记录，完全符合需求

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