row_number 模拟
在本教程中,您将学习如何在MySQL中模拟row_number函数。 我们将向您展示如何向每行或每组行添加行号。

row_number函数简介

row_number是一个排序函数,返回一行的顺序号,从第一行的1开始。经常想使用row_number函数来生成特定的报告。
MySQL不提供像SQL Server,Oracle或PostgreSQL中那样的row_number函数。 幸运的是,MySQL提供了可用于模拟row_number函数的会话变量。

MySQL row_number - 为每行添加行号

要模拟row_number函数,必须在查询中使用会话变量。
以下语句从employees表中获取5名员工,并从1开始为每行添加行号。
SET @row_number = 0;
SELECT
(@row_number:=@row_number + 1) AS num, firstName, lastName
FROM
employees
LIMIT 5;
执行上面语句,得到以下结果 -
+-----+-----------+-----------+
| num | firstName | lastName |
+-----+-----------+-----------+
| 1 | Diane | Murphy |
| 2 | Mary | Hill |
| 3 | Jeff | Firrelli |
| 4 | William | Patterson |
| 5 | Gerard | Bondur |
+-----+-----------+-----------+
5 rows in set
在上述查询语句中:
  • 在第一个语句中,定义了一个名为row_number的变量,并将其值设置为0row_number是由@前缀指示的会话变量。
  • 在第二个语句中,从employees表中查询选择了数据,并将row_number变量的值增加到每行的1LIMIT子句用于约束一些返回的行,在这种情况下,它被设置为5
另一种技术是使用会话变量作为派生表,并将其与主表连接。 请参阅以下查询:
SELECT
(@row_number:=@row_number + 1) AS num, firstName, lastName
FROM
employees,(SELECT @row_number:=0) AS t
LIMIT 5;
请注意,派生表必须具有自己的别名,以使查询语法正确。

MySQL row_number - 为每个组添加行号

如果要为每个组添加一个行号,并且为每个新组重置它。
我们来看看payments表:
SELECT
customerNumber, paymentDate, amount
FROM
payments
ORDER BY customerNumber;
执行上面查询语句,得到类似以下结果 -
+----------------+-------------+-----------+
| customerNumber | paymentDate | amount |
+----------------+-------------+-----------+
| 103 | 2014-10-19 | 6066.78 |
| 103 | 2013-06-05 | 14571.44 |
| 103 | 2014-12-18 | 1676.14 |
| 112 | 2014-12-17 | 14191.12 |
|*********** 省略部分数据 ******************|
| 496 | 2015-05-25 | 30253.75 |
| 496 | 2013-07-16 | 32077.44 |
| 496 | 2014-12-31 | 52166 |
+----------------+-------------+-----------+
273 rows in set
假设每个客户要添加一个行号,并且每当客户号码更改时,行号都会被重置。
要实现这一点,可使用两个会话变量,一个用于行号,另一个用于存储旧客户编号,以将其与当前的客户编号进行比较,如下查询语句:
SELECT
@row_number:=CASE
WHEN @customer_no = customerNumber THEN @row_number + 1
ELSE 1
END AS num,
@customer_no:=customerNumber as CustomerNumber,
paymentDate,
amount
FROM
payments
ORDER BY customerNumber;
在查询中使用了CASE语句。 如果客户号码保持不变,我们增加了row_number变量的值,否则将row_number变量重置为1。查询结果如下所示。
+-----+----------------+-------------+-----------+
| num | CustomerNumber | paymentDate | amount |
+-----+----------------+-------------+-----------+
| 1 | 103 | 2014-10-19 | 6066.78 |
| 2 | 103 | 2013-06-05 | 14571.44 |
| 3 | 103 | 2014-12-18 | 1676.14 |
| 1 | 112 | 2014-12-17 | 14191.12 |
| 2 | 112 | 2013-06-06 | 32641.98 |
| 3 | 112 | 2014-08-20 | 33347.88 |
| 1 | 114 | 2013-05-20 | 45864.03 |
| 2 | 114 | 2014-12-15 | 82261.22 |
| 3 | 114 | 2013-05-31 | 7565.08 |
| 4 | 114 | 2014-03-10 | 44894.74 |
| 1 | 119 | 2014-11-14 | 19501.82 |
| 2 | 119 | 2014-08-08 | 47924.19 |
| 3 | 119 | 2015-02-22 | 49523.67 |
| 1 | 121 | 2013-02-16 | 50218.95 |
|************* 此处省略了一大波数据 ***************|
| 1 | 496 | 2015-05-25 | 30253.75 |
| 2 | 496 | 2013-07-16 | 32077.44 |
| 3 | 496 | 2014-12-31 | 52166 |
+-----+----------------+-------------+-----------+
273 rows in set
row_number的行号一样,可以使用派生表和交叉连接技术来产生相同的结果。
Copy link
On this page
row_number函数简介
MySQL row_number - 为每行添加行号
MySQL row_number - 为每个组添加行号