SQL中如何编写带聚合函数的子查询找出每组第二大值？-SQL-PHP中文网

窗口函数ROW_NUMBER()最可靠，因聚合子查询在WHERE/SELECT中易报错或为空——聚合函数不能与非分组字段混用，且无法感知外层分组上下文。

sql中如何编写带聚合函数的子查询找出每组第二大值？

用窗口函数替代子查询更可靠

直接在 WHERE 或 SELECT 中嵌套聚合子查询来取“每组第二大值”，多数情况下会报错或返回空——因为聚合函数不能和非分组字段混用，且子查询无法天然感知外层分组上下文。MySQL 5.7 及更早、SQL Server 2012+、PostgreSQL、Oracle 都支持 ROW_NUMBER()，这是目前最稳的解法。

关键不是“怎么写子查询”，而是“为什么不该硬写聚合子查询”：比如 (SELECT MAX(score) FROM t t2 WHERE t2.group_id = t1.group_id AND score 这种写法在有重复最大值时就失效（第二大变成 NULL），且性能差、不可读。

ROW_NUMBER() 按组排序后给唯一序号，2 就是严格第二名（跳过并列）
若需“并列第二也保留”，改用 RANK()；若要“值相同时只算一个排名，跳过后续序号”，用 DENSE_RANK()
必须在子查询或 CTE 中先计算序号，再在外层 WHERE rn = 2 筛选，不能在 WHERE 里直接写窗口函数

MySQL 8.0+ 和 PostgreSQL 的标准写法

以学生表 students(group_id, name, score) 为例，查每组 score 第二高的记录（含姓名）：

SELECT group_id, name, score
FROM (
  SELECT group_id, name, score,
         ROW_NUMBER() OVER (PARTITION BY group_id ORDER BY score DESC) AS rn
  FROM students
) ranked
WHERE rn = 2;

注意：PARTITION BY group_id 是分组依据，ORDER BY score DESC 决定谁排前面；如果 score 相同，ROW_NUMBER() 会任意分配 1 和 2（稳定但不保证顺序），如需相同 score 排名一致，换 RANK()。

音潮

音潮是一款AI文本写作工具，全栈自研的AI音乐创作平台。

下载

MySQL 5.7 不支持窗口函数，只能用变量模拟或自连接，但逻辑复杂且易出错
PostgreSQL 对 NULL 默认排最后，如需 NULL 排最前，加 NULLS FIRST
如果只要每组第二大值（不要整行），可把 SELECT 改成 SELECT group_id, MAX(score)，但得配合 GROUP BY group_id 和子查询过滤，反而绕路

SQL Server 中遇到“无法对包含聚合或子查询的表达式执行聚合”错误

这个错误（Msg 130）常出现在试图写类似 SELECT group_id, (SELECT TOP 1 score FROM ... ORDER BY score DESC OFFSET 1 ROW) 却没加相关分组关联时。根本原因是子查询脱离了外层 GROUP BY 上下文。

正确做法仍是窗口函数：SQL Server 2012+ 完全支持 ROW_NUMBER()，写法与上例一致

若必须兼容老版本（如 SQL Server 2005），可用 OUTER APPLY + TOP 2 排序后取 TOP 1（排除最大值）：

SELECT t1.group_id, t2.score
FROM (SELECT DISTINCT group_id FROM students) t1
OUTER APPLY (
  SELECT TOP 1 score
  FROM students t2
  WHERE t2.group_id = t1.group_id
  ORDER BY score DESC OFFSET 1 ROW
) t2;