如何在SQL中使用相关子查询过滤每个分组的最大值-SQL-PHP中文网

不能直接用 GROUP BY 配合 MAX() 做行级过滤，因为 MAX() 仅返回聚合值，不保留对应行的其他字段（如 name），且非分组非聚合字段在严格模式下会报错；正确方法是用相关子查询或窗口函数。

如何在sql中使用相关子查询过滤每个分组的最大值

为什么不能直接用 GROUP BY 配合 MAX() 做行级过滤

想查“每个部门工资最高的人”，很多人第一反应是 SELECT dept, MAX(salary), name FROM emp GROUP BY dept —— 这会报错或返回错误的 name（MySQL 5.7+ 默认拒绝，PostgreSQL/SQL Server 直接报错）。因为 MAX(salary) 是聚合结果，而 name 不在 GROUP BY 中也没被聚合，数据库无法确定该取哪一行的 name。

相关子查询怎么写才对：用外层行驱动内层比较

核心思路是：对每一行，检查它的 salary 是否等于“同部门所有人的最高工资”。这时内层查询必须能访问外层的 dept 值，也就是“相关”（correlated）。

正确写法：

SELECT e1.name, e1.dept, e1.salary
FROM emp e1
WHERE e1.salary = (
  SELECT MAX(e2.salary)
  FROM emp e2
  WHERE e2.dept = e1.dept
);

e1 和 e2 是同一张表的两个别名，避免歧义
子查询里 WHERE e2.dept = e1.dept 是关键——没有这句就是全表最大值，加了才是“当前行所在部门”的最大值
如果一个部门有多个员工并列最高薪，这条语句会全部返回（符合业务预期）

遇到重复最大值、NULL 或性能问题怎么办

相关子查询逻辑清晰，但实际用时容易踩坑：

如果 salary 列允许 NULL，MAX() 会忽略它，但外层 e1.salary = NULL 永远不成立（NULL 不能用 = 比较），导致 NULL 行永远被过滤。需要额外处理：OR (e1.salary IS NULL AND NOT EXISTS (SELECT 1 FROM emp e2 WHERE e2.dept = e1.dept AND e2.salary IS NOT NULL))
数据量大时性能差：每行都触发一次子查询。有索引（如 (dept, salary) 联合索引）能缓解，但不如窗口函数高效
想只取一条（比如按姓名排序后取第一个），相关子查询本身做不到，得嵌套 ROW_NUMBER() 或改用 LIMIT 1 + ORDER BY（MySQL/PostgreSQL）

什么时候该换窗口函数而不是硬扛相关子查询

如果你用的是 PostgreSQL 8.4+、SQL Server 2005+、MySQL 8.0+ 或 Oracle，优先考虑 ROW_NUMBER() 或 RANK()：

SELECT name, dept, salary
FROM (
  SELECT name, dept, salary,
         RANK() OVER (PARTITION BY dept ORDER BY salary DESC) AS rk
  FROM emp
) t
WHERE rk = 1;