MariaDB ROW_NUMBER() 函数的基础用法与实例

MariaDB ROW_NUMBER() 函数用于为查询结果集中的每一行分配一个连续的行号。这个行号是基于指定的排序条件进行计算的。该函数常用于报表生成、分页查询等场景。

语法

MariaDB ROW_NUMBER() 函数的语法如下：

ROW_NUMBER() OVER ([PARTITION BY expr1,...] [ORDER BY expr2, ...])

PARTITION BY 子句用于指定分区表达式。该函数会为每个分区单独计算行号。
ORDER BY 子句用于指定行号的排序规则。在同一个分区内，行号按指定的排序顺序递增。

如果省略了 PARTITION BY，则对整个结果集计算行号。如果省略了 ORDER BY，则行号按任意顺序分配。

该函数会为每一行返回一个从 1 开始的行号。

实例

不分区，按照任意顺序编号

此示例演示了最简单的 ROW_NUMBER() 用法，为每一行编号。

DROP TABLE IF EXISTS mytable;
CREATE TABLE mytable (name VARCHAR(10), score INT);
INSERT INTO mytable VALUES
  ('John', 90), ('Amy', 92), ('Bob', 88), ('Kate', 95);

SELECT name, score, ROW_NUMBER() OVER() AS 'Row Number'
FROM mytable;

以下是该语句的输出：

+------+-------+------------+
| name | score | Row Number |
+------+-------+------------+
| Amy  |    92 |          1 |
| Bob  |    88 |          2 |
| John |    90 |          3 |
| Kate |    95 |          4 |
+------+-------+------------+

没有指定排序规则，所以行号随机分配。

不分区，按分数排序编号

此示例按分数升序为每一行编号。

SELECT name, score, ROW_NUMBER() OVER (ORDER BY score) AS 'Row Number'
FROM mytable;

以下是该语句的输出：

+------+-------+------------+
| name | score | Row Number |
+------+-------+------------+
| Bob  |    88 |          1 |
| John |    90 |          2 |
| Amy  |    92 |          3 |
| Kate |    95 |          4 |
+------+-------+------------+

按姓名分区，分数降序编号

此示例按姓名分区，并在每个分区内按分数降序编号。

SELECT name, score,
    ROW_NUMBER() OVER (PARTITION BY name ORDER BY score DESC) AS 'Row Number'
FROM mytable;

以下是该语句的输出：

+------+-------+------------+
| name | score | Row Number |
+------+-------+------------+
| Amy  |    92 |          1 |
| Bob  |    88 |          1 |
| John |    90 |          1 |
| Kate |    95 |          1 |
+------+-------+------------+

每个名字作为一个分区，分区内按分数降序排列并编号。

按城市和薪资级别分区编号

此示例模拟为一个公司的员工按城市和薪资级别编号。

DROP TABLE IF EXISTS employees;
CREATE TABLE employees (
  name VARCHAR(20),
  city VARCHAR(20),
  salary DECIMAL(10,2)
);

INSERT INTO employees VALUES
  ('John', 'Boston', 92000),
  ('Kathy', 'Boston', 85000),
  ('Amy', 'Dallas', 78000),
  ('Bob', 'Dallas', 92000);

SELECT name, city, salary,
  ROW_NUMBER() OVER (PARTITION BY city ORDER BY salary) AS 'Row Number'
FROM employees;

以下是该语句的输出：

+-------+--------+----------+------------+
| name  | city   | salary   | Row Number |
+-------+--------+----------+------------+
| Kathy | Boston | 85000.00 |          1 |
| John  | Boston | 92000.00 |          2 |
| Amy   | Dallas | 78000.00 |          1 |
| Bob   | Dallas | 92000.00 |          2 |
+-------+--------+----------+------------+

根据城市和每 20000 美元一个薪资级别分区，并在每个分区内按薪资升序编号。

结合 RANK() 函数生成报表

ROW_NUMBER() 常与其他窗口函数结合使用生成报表。

DROP TABLE IF EXISTS sales;
CREATE TABLE sales (product VARCHAR(20), amount INT);
INSERT INTO sales VALUES
  ('Beverage', 2500), ('Bread', 3000), ('Beverage', 2800),
  ('Cereal', 1200), ('Bread', 2200), ('Cereal', 2000);

SELECT product, amount,
  ROW_NUMBER() OVER (ORDER BY amount DESC) AS 'Row Number',
  RANK() OVER (ORDER BY amount DESC) AS 'Rank'
FROM sales
GROUP BY product, amount
ORDER BY amount DESC;

以下是该语句的输出：

+----------+--------+------------+------+
| product  | amount | Row Number | Rank |
+----------+--------+------------+------+
| Bread    |   3000 |          1 |    1 |
| Beverage |   2800 |          2 |    2 |
| Beverage |   2500 |          3 |    3 |
| Bread    |   2200 |          4 |    4 |
| Cereal   |   2000 |          5 |    5 |
| Cereal   |   1200 |          6 |    6 |
+----------+--------+------------+------+

该查询为每个产品和销量组合编号，并计算出排名。

结论

MariaDB ROW_NUMBER() 是一个十分实用的窗口函数。它能够为查询结果集中的每一行分配一个连续的行号，行号的计算基于指定的分区和排序条件。结合其他窗口函数，可以方便地生成排名报表和其他分析型查询结果。在需要为结果集分配序号或生成分页数据的场景下，ROW_NUMBER() 会是个非常好的选择。当然，使用时也需注意分区和排序条件的选择，确保结果符合预期。总的来说，ROW_NUMBER() 是一个非常有用且高效的行号分配函数。

语法

实例

不分区，按照任意顺序编号

不分区，按分数排序编号

按姓名分区，分数降序编号

按城市和薪资级别分区编号

结合 RANK() 函数生成报表

相关函数

结论