MySQL关联表时每个用户仅返回count最大的单条记录问题

原查询的错误原因 #

你的子查询(SELECT MAX(count) FROM user_codes GROUP BY b.user_id)会返回所有用户的最大count值列表（比如1000、15、22），但用=进行比较时，数据库只会取这个列表的第一个值（1000）去匹配，所以只有user_id=1的记录能关联成功，其他用户的count不等于1000，自然返回NULL。

正确的查询写法 #

方法一：使用窗口函数（推荐，简洁高效） #

利用ROW_NUMBER()窗口函数，按user_id分组后给每条记录按count降序排名，然后取排名为1的记录：

SELECT u.id, u.email, uc.invite_code, uc.count
FROM users u
LEFT JOIN (
    SELECT 
        user_id, 
        invite_code, 
        count,
        ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY count DESC) AS rn
    FROM user_codes
) uc ON u.id = uc.user_id AND uc.rn = 1;

如果存在同一user_id下多个count值相同的最大值，ROW_NUMBER()会随机选一条；若想返回所有最大值记录，可替换成RANK()。

方法二：先分组获取每个用户的最大count，再关联 #

先查询出每个user_id对应的最大count，再用这个结果和user_codes、users表关联：

SELECT u.id, u.email, uc.invite_code, uc.count
FROM users u
LEFT JOIN (
    SELECT user_id, MAX(count) AS max_count
    FROM user_codes
    GROUP BY user_id
) mc ON u.id = mc.user_id
LEFT JOIN user_codes uc ON mc.user_id = uc.user_id AND mc.max_count = uc.count;

这种方法在存在同一用户多个最大count记录时，会返回多条结果，可根据需求调整。

验证结果 #

以上两种写法都能得到你预期的结果：

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