MySQL 8.0不支持NTH_VALUE函数,执行会报错“FUNCTION does not exist”;即使高版本需显式指定窗口帧(如ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)和ORDER BY,否则报错或返回NULL。

为什么NTH_VALUE在MySQL中直接报错
因为MySQL 8.0之前压根不支持NTH_VALUE,而即使升级到8.0+,它也**必须配合窗口帧(frame clause)使用**,否则会报错Window function 'nth_value' requires an OVER clause with a frame specification。很多人复制其他数据库的写法,漏掉ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING就直接失败。
正确写法:带完整窗口定义和降序排序
NTH_VALUE本身不排序,只按窗口内已排好序的顺序取值。想取“第二高”,必须先按薪资DESC排序,再取第2个。同时窗口帧必须显式声明覆盖全部行,否则默认是ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW,导致只有当前行及之前行参与计算,第二高的值很可能还没出现。
正确示例:
SELECT dept,
NTH_VALUE(salary, 2) OVER (
PARTITION BY dept
ORDER BY salary DESC
ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
) AS second_highest_salary
FROM employees;
- 不加
PARTITION BY dept会导致全表混算,结果无部门区分 - 漏掉
ROWS ...会触发报错或返回NULL(尤其当第二高在当前行之后时) -
ORDER BY salary DESC不能写成ASC,否则取到的是第二低
更稳妥的替代方案:用DENSE_RANK避免重复值干扰
如果某部门有多个员工薪资并列最高(比如3人都是20k),NTH_VALUE(..., 2)会跳过并列项直接取第三个不同值,但实际业务中常要求“第二高的薪资数值”(即去重后第二大的数),这时NTH_VALUE不可靠。
推荐用DENSE_RANK():
SELECT dept, salary AS second_highest_salary
FROM (
SELECT dept,
salary,
DENSE_RANK() OVER (PARTITION BY dept ORDER BY salary DESC) AS rk
FROM employees
) t
WHERE rk = 2;
-
DENSE_RANK对相同薪资赋予相同排名(如20k、20k、18k → 1、1、2),真正反映“第二高薪资数值” -
RANK()会产生跳跃(1、1、3),ROW_NUMBER()则强制唯一编号(1、2、3),都不符合“第二高薪资”的语义 - 子查询是必须的——窗口函数不能直接出现在
WHERE中
Oracle/PostgreSQL用户注意兼容性差异
Oracle 12c+ 和 PostgreSQL 13+ 虽然支持NTH_VALUE,但默认窗口帧行为不同:Oracle 默认RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW,PostgreSQL 默认同MySQL 8.0需显式声明。跨数据库迁移时,哪怕SQL语法通过,结果也可能因帧范围不同而错位。
最保险的做法始终是:显式写出ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING,并确认ORDER BY方向与业务目标一致。
实际跑起来之前,先用SELECT dept, salary, DENSE_RANK() OVER (PARTITION BY dept ORDER BY salary DESC) AS rk FROM employees看一眼排名分布——重复值怎么排、空值在哪、边界数据是否合理,比死磕NTH_VALUE参数更容易定位问题。










