MySQL 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的變數，並將其值設定為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的行號一樣，可以使用派生表和交叉連線技術來產生相同的結果。