MySQL ROW_NUMBER详解

引言

MySQL 是一种广泛使用的关系型数据库管理系统。在进行数据查询和分析时，我们经常需要对数据进行排序并为每一行分配唯一的行号。在某些情况下，我们可能还需要根据这些行号进行筛选和处理。本文将详解 MySQL 中的 ROW_NUMBER() 函数，它可以帮助我们实现这些需求。

什么是 ROW_NUMBER() 函数

ROW_NUMBER() 函数是 MySQL 提供的一种窗口函数。它为查询结果中的每一行分配一个唯一的行号。可以通过该行号对查询结果进行排序、筛选和分组等操作。

ROW_NUMBER() 函数的语法

ROW_NUMBER() OVER ([PARTITION BY col1, col2, ... ORDER BY col1, col2, ...])

ROW_NUMBER()：函数名称。
OVER：表示该函数为窗口函数。在 OVER 子句中可以定义分区（PARTITION BY）和排序（ORDER BY）。
PARTITION BY：可选参数，表示按照指定的列进行分组。
ORDER BY：可选参数，表示按照指定的列进行排序。

ROW_NUMBER() 函数的使用示例

创建测试表

为了演示 ROW_NUMBER() 函数的使用，我们首先创建一个名为 “students” 的测试表，该表存储了学生信息。

CREATE TABLE students (
  id INT PRIMARY KEY,
  name VARCHAR(50),
  age INT,
  score FLOAT
);

插入测试数据

向测试表中插入一些测试数据，方便后续查询和操作。

INSERT INTO students (id, name, age, score) VALUES
(1, 'Tom', 18, 90.5),
(2, 'Lily', 19, 85.5),
(3, 'John', 20, 92),
(4, 'Emma', 18, 88),
(5, 'Mike', 19, 91.5),
(6, 'Lucy', 18, 87.5);

基本使用

基本使用方式是在 SELECT 语句中嵌套 ROW_NUMBER() 函数，并使用 ORDER BY 子句指定排序方式。下面的示例将根据学生的分数对数据进行排序，并为每一行分配唯一的行号。

SELECT 
  ROW_NUMBER() OVER (ORDER BY score) AS row_number,
  name,
  score
FROM students;

以下是上述查询的结果：

+------------+------+-------+
| row_number | name | score |
+------------+------+-------+
|    1       | Lucy |  87.5 |
|    2       | Lily |  85.5 |
|    3       | Emma |  88.0 |
|    4       | Tom  |  90.5 |
|    5       | Mike |  91.5 |
|    6       | John |  92.0 |
+------------+------+-------+

ROW_NUMBER() 结合分区

当需要为某一列的值相同的行分配连续的行号时，可以使用 PARTITION BY 子句进行分区。下面的示例将根据学生的年龄对数据进行分区，并按照分区后的结果分配行号。

SELECT 
  ROW_NUMBER() OVER (PARTITION BY age ORDER BY score DESC) AS row_number,
  name,
  age,
  score
FROM students;

以下是上述查询的结果：

+------------+------+-----+-------+
| row_number | name | age | score |
+------------+------+-----+-------+
|    1       | John |  20 |  92.0 |
|    1       | Lily |  19 |  85.5 |
|    2       | Mike |  19 |  91.5 |
|    1       | Emma |  18 |  88.0 |
|    2       | Lucy |  18 |  87.5 |
|    3       | Tom  |  18 |  90.5 |
+------------+------+-----+-------+

ROW_NUMBER() 结合分区和排序

当需要同时按照某一列进行分区和排序时，可以在 ORDER BY 子句中使用多列。下面的示例将根据学生的年龄和分数对数据进行分区和排序，并按照分区后的结果分配行号。

SELECT 
  ROW_NUMBER() OVER (PARTITION BY age ORDER BY score DESC, name) AS row_number,
  name,
  age,
  score
FROM students;

以下是上述查询的结果：

+------------+------+-----+-------+
| row_number | name | age | score |
+------------+------+-----+-------+
|    1       | John |  20 |  92.0 |
|    1       | Lily |  19 |  85.5 |
|    2       | Mike |  19 |  91.5 |
|    1       | Emma |  18 |  88.0 |
|    2       | Lucy |  18 |  87.5 |
|    3       | Tom  |  18 |  90.5 |
+------------+------+-----+-------+

使用 ROW_NUMBER() 进行分页

ROW_NUMBER() 函数还可以用于分页查询，通过设定合适的分页参数，可以在查询结果中获取指定页的数据。下面的示例演示了如何使用 ROW_NUMBER() 函数实现分页查询。

SELECT 
  name,
  age,
  score
FROM (
  SELECT 
    ROW_NUMBER() OVER (ORDER BY id) AS row_number,
    name,
    age,
    score
  FROM students
) AS tmp
WHERE row_number BETWEEN 2 AND 4;

以下是上述查询的结果：

+------+-----+-------+
| name | age | score |
+------+-----+-------+
| Lily |  19 |  85.5 |
| John |  20 |  92.0 |
| Emma |  18 |  88.0 |
+------+-----+-------+