MySQL 中 Row_Number() 函式的使用
-
在 MySQL 中使用
ROW_NUMBER()
使用ORDER BY
子句 -
在 MySQL 中使用
PARTITION BY
子句使用ROW_NUMBER()
-
在 MySQL 中使用
PARTITION BY
和ORDER BY
子句使用ROW_NUMBER()
-
在 MySQL 中使用會話變數複製
ROW_NUMBER()
- まとめ
在本教程中,我們將介紹如何在 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
進行排名目的.