MySQL 中 Row_Number() 函式的使用

1. 在 MySQL 中使用 ROW_NUMBER() 使用 ORDER BY 子句
2. 在 MySQL 中使用 PARTITION BY 子句使用 ROW_NUMBER()
3. 在 MySQL 中使用 PARTITION BY 和 ORDER BY 子句使用 ROW_NUMBER()
4. 在 MySQL 中使用會話變數複製 ROW_NUMBER()
5. まとめ

在本教程中，我們將介紹如何在 MySQL 中使用 ROW_NUMBER() 函式。這是一種排序方法，在分割槽內從 1 開始分配連續的數字。需要注意的是，分割槽內的兩行沒有相同的數字。

我們還將看到 PARTITION BY 和 ORDER BY 如何影響 MySQL 結果。你必須使用 ORDER BY 子句來使用 ROW_NUMBER()，因為它是強制性的。但是 PARTITION BY 子句是可選的。

如果同時使用 PARTITION BY 和 ORDER BY 這兩個子句，結果將是不確定的。在這裡，我們將看到如何使用會話變數來模擬 ROW_NUMBER() 函式以獲得所需的結果。

請注意 ROW_NUMBER() 在 MySQL 8.0 版之前不可用。你會看到 MySQL 8.0 版中的新功能此處。

在 MySQL 中使用 ROW_NUMBER() 使用 ORDER BY 子句

我們將只使用帶有 ORDER BY 子句的 ROW_NUMBER() 函式並觀察結果。讓我們首先建立表並在其中填充一些資料。

示例程式碼：

# SQL Programming Using MySQL Version 8.27
CREATE TABLE `test_db`.`tb_student` (
 STUDENT_ID	INTEGER NOT NULL,
 FIRST_NAME	VARCHAR(30) NOT NULL,
 LAST_NAME	VARCHAR(30) NOT NULL,
 GENDER	VARCHAR(30) NOT NULL,
 CITY_NAME	VARCHAR(64) NOT NULL,
 EMAIL_ADDRESS	VARCHAR(64) NOT NULL,
 REGISTRATION_YEAR INTEGER NOT NULL,
 PRIMARY KEY	(STUDENT_ID)
);

此查詢將建立一個名為 tb_student 的表，你可以在 MySQL 資料庫中確認該表。

使用 INSERT 查詢的以下語法將六條記錄插入名為 tb_student 的表中。

# SQL Programming Using MySQL Version 8.27
INSERT INTO test_db.tb_student
(STUDENT_ID, FIRST_NAME, LAST_NAME, GENDER, CITY_NAME, EMAIL_ADDRESS, REGISTRATION_YEAR)
VALUES
(1,'Ayush','Kumar', 'Male', 'Washington', 'akuman@yahoo.com', 2010);

然後從表中選擇所有資料以使用以下查詢進行檢視。

# SQL Programming Using MySQL Version 8.27
SELECT * FROM test_db.tb_student

你的表將包含以下資料。你還可以檢查並比較。

# SQL Programming Using MySQL Version 8.27
SELECT *,
    ROW_NUMBER() OVER(ORDER BY REGISTRATION_Year) AS row_numb
FROM test_db.tb_student;

執行上述查詢後，你將得到以下結果。

觀察上面的輸出，你會看到所有的記錄都顯示出來了，這些記錄是按註冊年份排序的（見綠色框內的列）。並且 row_number 也與預期相同，從 1 開始並隨著我們從 tb_student 讀取所有資料而不斷增加直到表的末尾。

在 MySQL 中使用 PARTITION BY 子句使用 ROW_NUMBER()

我們將只使用帶有 PARTITION BY 子句的 ROW_NUMBER() 函式並觀察結果。我們還將將此輸出與使用 ROW_NUMBER() 和 ORDER BY 子句獲得的結果進行比較。

示例程式碼：

# SQL Programming Using MySQL Version 8.27
SELECT *,
    ROW_NUMBER() OVER(PARTITION BY REGISTRATION_Year) AS row_numb
FROM test_db.tb_student;

現在，你將得到以下結果。

檢視 REGISTRATION_YEAR 列；它有 5 個分割槽（2010、2011、2012、2013 和 2014）。partition 2010 的表中有兩行，並且行號分配正確（再次參見上面的螢幕截圖）。partition 2011, 2012, 2013, 2014 只有一行；這就是為什麼你可以在 row_numb 列中看到 1。

如果我們使用 PARTITION BY 子句，那麼為什麼名為 REGISTRATION_YEAR 的列按升序排列？因為 PARTITION BY 子句對這些分割槽中的資料進行排序。讓我們插入另一條記錄，其 REGISTRATION_YEAR 的值為 2009 並觀察結果。

# SQL Programming Using MySQL Version 8.27

INSERT INTO test_db.tb_student
(STUDENT_ID, FIRST_NAME, LAST_NAME, GENDER, CITY_NAME, EMAIL_ADDRESS, REGISTRATION_YEAR)
VALUES
(7,'Mashal','Naaz', 'Female', 'Florida', 'mashalnaaz@gmail.com', 2009);

SELECT *,
    ROW_NUMBER() OVER(PARTITION BY REGISTRATION_Year) AS row_numb
FROM test_db.tb_student;

現在，你將看到最近的記錄位於頂部。

在 MySQL 中使用 PARTITION BY 和 ORDER BY 子句使用 ROW_NUMBER()

現在，我們將只使用帶有 PARTITION BY 和 ORDER BY 子句的 ROW_NUMBER() 函式，看看它是否仍然提供正確的行號。

示例程式碼：

# SQL Programming Using MySQL Version 8.27
SELECT *,
    ROW_NUMBER() OVER(PARTITION BY REGISTRATION_YEAR ORDER BY REGISTRATION_YEAR) AS row_numb
FROM test_db.tb_student;

執行上述查詢後，你將看到與使用 ROW_NUMBER() 和 PARTITION BY 子句相同的輸出。請參閱以下螢幕截圖：

看到黃色背景的列，這是我們所期望的。在這裡，我們將使用會話變數來正確分配行號。

在 MySQL 中使用會話變數複製 ROW_NUMBER()

當我們同時使用 PARTITION BY 和 ORDER BY 子句時，MySQL 不提供正確的排名功能。在這種情況下，我們使用會話變數來模擬它。會話變數是使用者定義的；你可以在此處檢視詳細資訊。

示例程式碼：

# SQL Programming Using MySQL Version 8.27
SET @row_numb = 0;
SELECT *,
    (@row_numb:=@row_numb + 1) AS row_numb
FROM test_db.tb_student ORDER BY REGISTRATION_YEAR;

正如你在下面看到的，row_numb 從 1 開始並連續增加。

它是如何工作的？我們首先使用@字首設定會話變數 row_numb 並使用 0 進行初始化。然後我們從表中選擇資料，對其排序並列印它。 (@row_numb:=@row_numb + 1) 就像增加和更新變數的值一樣。

まとめ

根據上述討論，我們得出結論，雖然我們可以在 MySQL 中使用 ROW_NUMBER() 函式，如果我們有 8.0 或更高版本，但在某些情況下我們必須使用 Session Variables 進行排名目的.