MySQL NTH_VALUE 函数

---

简介：在本教程中，您将学习如何使用NTH_VALUE()函数从结果集中的第N行获取值。 这NTH_VALUE()是一个窗口函数，允许您从有序行集中的第N行获取值。 以下显示了NTH_VALUE()函数的语法：

NTH_VALUE(expression, N)
FROM FIRST
OVER (
    partition_clause
    order_clause
    frame_clause
)

NTH_VALUE()函数返回expression窗口框架第N行的值。如果第N行不存在，则函数返回NULL。N必须是正整数，例如1,2和3。 FROM FIRST指示NTH_VALUE()功能在窗口帧的第一行开始计算。 请注意，SQL标准支持FROM FIRST和FROM LAST。但是，MySQL只支持FROM FIRST。如果要模拟效果FROM LAST，则可以使用其中ORDER BY的over_clause相反顺序对结果集进行排序。

---

MySQL NTH_VALUE() 函数示例

我们将创建一个以basic_pay演示命名的新表。

CREATE TABLE basic_pays(
    employee_name VARCHAR(50) NOT NULL,
    department VARCHAR(50) NOT NULL,
    salary INT NOT NULL,
    PRIMARY KEY (employee_name , department)
);
 
INSERT INTO 
 basic_pays(employee_name, 
    department, 
    salary)
VALUES
 ('Diane Murphy','Accounting',8435),
 ('Mary Patterson','Accounting',9998),
 ('Jeff Firrelli','Accounting',8992),
 ('William Patterson','Accounting',8870),
 ('Gerard Bondur','Accounting',11472),
 ('Anthony Bow','Accounting',6627),
 ('Leslie Jennings','IT',8113),
 ('Leslie Thompson','IT',5186),
 ('Julie Firrelli','Sales',9181),
 ('Steve Patterson','Sales',9441),
 ('Foon Yue Tseng','Sales',6660),
 ('George Vanauf','Sales',10563),
 ('Loui Bondur','SCM',10449),
 ('Gerard Hernandez','SCM',6949),
 ('Pamela Castillo','SCM',11303),
 ('Larry Bott','SCM',11798),
 ('Barry Jones','SCM',10586);

NTH_VALUE()在结果集上使用MySQL 函数

以下语句使用NTH_VALUE()函数查找薪水第二高的员工：

SELECT
    employee_name,
    salary,
    NTH_VALUE(employee_name, 2) OVER  (
        ORDER BY salary DESC
    ) second_highest_salary
FROM
    basic_pays;

这是输出：

+-------------------+--------+-----------------------+
| employee_name     | salary | second_highest_salary |
+-------------------+--------+-----------------------+
| Larry Bott        |  11798 | NULL                  |
| Gerard Bondur     |  11472 | Gerard Bondur         |
| Pamela Castillo   |  11303 | Gerard Bondur         |
| Barry Jones       |  10586 | Gerard Bondur         |
| George Vanauf     |  10563 | Gerard Bondur         |
| Loui Bondur       |  10449 | Gerard Bondur         |
| Mary Patterson    |   9998 | Gerard Bondur         |
| Steve Patterson   |   9441 | Gerard Bondur         |
| Julie Firrelli    |   9181 | Gerard Bondur         |
| Jeff Firrelli     |   8992 | Gerard Bondur         |
| William Patterson |   8870 | Gerard Bondur         |
| Diane Murphy      |   8435 | Gerard Bondur         |
| Leslie Jennings   |   8113 | Gerard Bondur         |
| Gerard Hernandez  |   6949 | Gerard Bondur         |
| Foon Yue Tseng    |   6660 | Gerard Bondur         |
| Anthony Bow       |   6627 | Gerard Bondur         |
| Leslie Thompson   |   5186 | Gerard Bondur         |
+-------------------+--------+-----------------------+
17 rows in set (0.01 sec)

使用MySQL而NTH_VALUE() 不是分区示例

以下查询查找每个部门中薪水第二高的员工：

SELECT
 employee_name,
 department,
 salary,
 NTH_VALUE(employee_name, 2) OVER  (
 PARTITION BY department
 ORDER BY salary DESC
 RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
 ) second_highest_salary
FROM
 basic_pays;

这是输出：

+-------------------+------------+--------+-----------------------+
| employee_name     | department | salary | second_highest_salary |
+-------------------+------------+--------+-----------------------+
| Gerard Bondur     | Accounting |  11472 | Mary Patterson        |
| Mary Patterson    | Accounting |   9998 | Mary Patterson        |
| Jeff Firrelli     | Accounting |   8992 | Mary Patterson        |
| William Patterson | Accounting |   8870 | Mary Patterson        |
| Diane Murphy      | Accounting |   8435 | Mary Patterson        |
| Anthony Bow       | Accounting |   6627 | Mary Patterson        |
| Leslie Jennings   | IT         |   8113 | Leslie Thompson       |
| Leslie Thompson   | IT         |   5186 | Leslie Thompson       |
| George Vanauf     | Sales      |  10563 | Steve Patterson       |
| Steve Patterson   | Sales      |   9441 | Steve Patterson       |
| Julie Firrelli    | Sales      |   9181 | Steve Patterson       |
| Foon Yue Tseng    | Sales      |   6660 | Steve Patterson       |
| Larry Bott        | SCM        |  11798 | Pamela Castillo       |
| Pamela Castillo   | SCM        |  11303 | Pamela Castillo       |
| Barry Jones       | SCM        |  10586 | Pamela Castillo       |
| Loui Bondur       | SCM        |  10449 | Pamela Castillo       |
| Gerard Hernandez  | SCM        |   6949 | Pamela Castillo       |
+-------------------+------------+--------+-----------------------+
17 rows in set (0.01 sec)

在此查询中，我们添加了PARTITION BY按部门划分员工的子句。然后NTH_VALUE()函数独立地应用于每个分区。 在本教程中，您学习了如何使用MySQL NTH_VALUE()函数从结果集的第N行获取值。

---