| 在本教程中，您将学习如何在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的变量，并将其值设置为0。 row_number是由@前缀指示的会话变量。
在第二个语句中，从employees表中查询选择了数据，并将row_number变量的值增加到每行的1。LIMIT子句用于约束一些返回的行，在这种情况下，它被设置为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的行号一样，可以使用派生表和交叉连接技术来产生相同的结果。 |
