使用MAX()从关联表筛选分组特定值是否为最优查询方案？

原代码的性能瓶颈 #

你的代码逻辑能得到正确结果，但核心问题在于相关子查询：外部查询的每一行都会触发一次子查询，重复计算对应place的最大余额，数据量越大，重复计算的开销就越高，性能会急剧下降。另外DISTINCT的使用其实是冗余的——用更合理的写法可以避免结果重复。

高效方案1：窗口函数（推荐，现代SQL数据库通用） #

窗口函数是处理分组TOPN场景的最优解，仅需一次表扫描即可完成计算，性能远优于相关子查询。

代码实现 #

SELECT place, name, balance
FROM (
    SELECT 
        w.place, 
        acc.name, 
        acc.balance,
        -- 按place分组，组内按余额降序排号
        ROW_NUMBER() OVER (PARTITION BY w.place ORDER BY acc.balance DESC) AS rn
    FROM withdrawals w
    INNER JOIN acc ON w.user_id = acc.user_id
) t
-- 只取每组排名第一的记录
WHERE rn = 1;

逻辑拆解 #

• PARTITION BY w.place：把全表数据按place拆分成独立分组，对应你编程思维里的“循环遍历每个place分组”
• ORDER BY acc.balance DESC：在每个分组内，按余额从大到小排序，确保最大余额的记录排在最前
• ROW_NUMBER()：给每个分组内的行分配唯一序号，最大余额的行序号为1
• 外层筛选rn=1，直接得到每个place下余额最大的记录

如果同一place下有多个用户余额相同且都是最大值，想保留所有这类记录，把ROW_NUMBER()换成RANK()或DENSE_RANK()即可：

• RANK()：相同余额会获得相同排名，比如两个第一后，下一个是第三
• DENSE_RANK()：相同余额相同排名，下一个是第二

高效方案2：JOIN+聚合（兼容老版本数据库） #

如果你的数据库不支持窗口函数（如MySQL 5.7及更早版本），可以用“先聚合求分组最大值，再关联原表”的方法，仅需两次表扫描，性能远优于相关子查询。

代码实现 #

SELECT w.place, acc.name, acc.balance
FROM withdrawals w
INNER JOIN acc ON w.user_id = acc.user_id
INNER JOIN (
    -- 一次性计算所有place的最大余额
    SELECT w_inner.place, MAX(acc_inner.balance) AS max_balance
    FROM withdrawals w_inner
    INNER JOIN acc acc_inner ON w_inner.user_id = acc_inner.user_id
    GROUP BY w_inner.place
) t ON w.place = t.place AND acc.balance = t.max_balance;

逻辑拆解 #

1. 子查询先一次性计算出所有place对应的最大余额，相当于提前把所有分组的结果缓存好，避免重复计算
2. 将原表与子查询结果关联，筛选出place匹配且余额等于对应分组最大值的记录

这种方法会保留同一place下所有余额等于最大值的记录，效果和RANK()一致。

性能对比 #

• 原代码：相关子查询，时间复杂度O(n*m)（n为主表行数，m为分组数），数据量大时极慢
• 窗口函数：时间复杂度O(n log n)（仅排序开销），一次表扫描，性能最优
• JOIN+聚合：时间复杂度O(n)+O(n)，两次表扫描，性能接近窗口函数，适合老版本数据库

思维转变提示 #

从编程的循环逻辑转到SQL的集合思维：

• 编程是“遍历每个元素→判断条件→处理结果”
• SQL是“定义数据集→对数据集做分组/过滤/排序等批量操作”
• 不要用“循环每个place找最大值”的思路，而是用“一次性计算所有分组最大值，再匹配对应数据”的思路

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