MySQL 中 Row_Number() 函式的使用

Mehvish Ashiq 2023年1月30日 2022年5月13日
  1. 在 MySQL 中使用 ROW_NUMBER() 使用 ORDER BY 子句
  2. 在 MySQL 中使用 PARTITION BY 子句使用 ROW_NUMBER()
  3. 在 MySQL 中使用 PARTITION BYORDER BY 子句使用 ROW_NUMBER()
  4. 在 MySQL 中使用會話變數複製 ROW_NUMBER()
  5. まとめ
MySQL 中 Row_Number() 函式的使用

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

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

如果同時使用 PARTITION BYORDER 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 資料庫中確認該表。

mysql 中的 row_number - 表建立

使用 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

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

mysql 中的 row_number - 填充表

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

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

mysql 中的 row_number - 帶有 order by 子句的 row_number

觀察上面的輸出,你會看到所有的記錄都顯示出來了,這些記錄是按註冊年份排序的(見綠色框內的列)。並且 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;

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

mysql 中的 row_number - 帶有 partition by 子句的 row_number

檢視 REGISTRATION_YEAR 列;它有 5 個分割槽(20102011201220132014)。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 中的 row_number - 帶有 partition by 子句的 row_number

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

現在,我們將只使用帶有 PARTITION BYORDER 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 - 帶有兩個子句的 row_number

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

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

當我們同時使用 PARTITION BYORDER 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 開始並連續增加。

mysql 中的 row_number - 會話變數

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

まとめ

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

Mehvish Ashiq avatar Mehvish Ashiq avatar

Mehvish Ashiq is a former Java Programmer and a Data Science enthusiast who leverages her expertise to help others to learn and grow by creating interesting, useful, and reader-friendly content in Computer Programming, Data Science, and Technology.

LinkedIn GitHub Facebook