MAX()子查询可替代自连接实现分组取最大值对应整行，关键在于用相关子查询比较每行值与所在分组MAX值，而非全局MAX；需联合索引支撑性能。
MAX() 本身不能“避免自连接”，但它在配合子查询或窗口函数时，能替代某些原本必须用自连接才能实现的逻辑——尤其是“查每组中某字段最大值对应整行数据”这类需求。盲目套用 MAX() 反而会出错，关键在于怎么组合、在哪一层用。
为什么直接 SELECT * FROM t WHERE col = (SELECT MAX(col) FROM t) 不够用
这个写法只能返回**全局最大值的那一行**，无法按分组（比如每个部门、每个用户、每天）分别取最大值对应的完整记录。一旦加了 GROUP BY，又没法直接带出非聚合字段，SQL 会报错：ERROR 1055: Expression #2 of SELECT list is not in GROUP BY clause。
- 它只适用于“整个表里找一条最值记录”的极简场景
- 业务中更常见的是“每个部门薪资最高的人是谁”，这需要关联原始行，不是单纯一个数字
- 若强行在子查询里
JOIN回原表，就又绕回自连接的老路
用相关子查询 + MAX() 替代自连接的正确姿势
核心思路：对每一行，检查它的目标字段值是否等于“它所在分组的该字段最大值”。不生成中间笛卡尔积，也不依赖窗口函数排序开销。
- 假设要查每个
department_id下salary最高的员工完整信息 - 子查询必须是“相关”的，即引用外层别名（如
s1.department_id） - 子查询只返回一个标量值（
MAX(s2.salary)），数据库可高效利用索引
SELECT s1.id, s1.name, s1.department_id, s1.salary FROM employees s1 WHERE s1.salary = ( SELECT MAX(s2.salary) FROM employees s2 WHERE s2.department_id = s1.department_id );
注意：这个查询要求 (department_id, salary) 上有联合索引，否则子查询会退化为对每个 s1 行都扫一遍全表。
当存在并列最大值时，MAX() 子查询的行为与陷阱
如果同一部门有多个员工薪资同为最高，MAX() 子查询会全部返回——这是正确行为，但容易被误认为“没去重”或“结果不稳定”。实际它完全符合语义：“所有达到本组最高薪资的记录”。
- 不需要额外加
LIMIT 1，那会随机截断，丢失业务意义 - 若业务明确只要一条（比如取
id最小的那个），得改用ROW_NUMBER() OVER (PARTITION BY department_id ORDER BY salary DESC, id ASC) -
MAX()子查询在 MySQL 8.0+、PostgreSQL、SQL Server 中执行计划通常显示为DEPENDENT SUBQUERY，只要索引到位，性能远优于等价的自连接
比 MAX() 子查询更快的替代：用窗口函数但避开排序陷阱
虽然你问的是 MAX()，但真实高并发场景下，纯子查询可能仍不够快。此时可考虑窗口函数，但必须绕开全分区排序这个坑：
- 不要写
ROW_NUMBER() OVER (PARTITION BY dept ORDER BY salary DESC)然后WHERE rn = 1—— 它强制排序整个分区 - 改用
MAX(salary) OVER (PARTITION BY department_id)计算每行所属分组的最大值，再和本行比较 - 这个写法不触发排序，执行计划里是
WindowAgg+Seq Scan，对千万级表也稳定在百毫秒内
SELECT id, name, department_id, salary
FROM (
SELECT id, name, department_id, salary,
MAX(salary) OVER (PARTITION BY department_id) AS max_in_dept
FROM employees
) t
WHERE salary = max_in_dept;
最后一句必须强调：无论选子查询还是窗口函数，department_id 和 salary 的联合索引是前提。没有它，所有优化都是纸上谈兵。
