Verwendung der Funktion Row_Number() in MySQL

1. Verwendung von ROW_NUMBER() in MySQL mit der ORDER BY-Klausel
2. Verwendung von ROW_NUMBER() in MySQL mit der PARTITION BY-Klausel
3. Verwendung von ROW_NUMBER() in MySQL mit PARTITION BY- und ORDER BY-Klausel
4. Replikation von ROW_NUMBER() in MySQL mit Sitzungsvariable
5. Fazit

In diesem Tutorial stellen wir Ihnen vor, wie Sie die Funktion ROW_NUMBER() in MySQL verwenden können. Es handelt sich um eine Ranking-Methode, die fortlaufende Nummern innerhalb der Partition ab 1 zuweist. Es ist wichtig zu beachten, dass zwei Zeilen innerhalb der Partition nicht dieselbe Nummer haben.

Wir werden auch sehen, wie sich PARTITION BY und ORDER BY auf die MySQL-Ergebnisse auswirken. Sie müssen die Klausel ORDER BY verwenden, um ROW_NUMBER() zu verwenden, da dies obligatorisch ist. Aber die Klausel PARTITION BY ist optional.

Die Ergebnisse sind unbestimmt, wenn Sie beide Klauseln verwenden, PARTITION BY und ORDER BY. Hier sehen wir, wie die Funktion ROW_NUMBER() mithilfe der Session-Variablen emuliert wird, um die gewünschten Ergebnisse zu erzielen.

Bitte beachten Sie, dass ROW_NUMBER() vor MySQL Version 8.0 nicht verfügbar war. Was in MySQL Version 8.0 neu ist, sehen Sie hier.

Verwendung von ROW_NUMBER() in MySQL mit der ORDER BY-Klausel

Wir werden nur die Funktion ROW_NUMBER() mit der Klausel ORDER BY verwenden und die Ergebnisse beobachten. Lassen Sie uns zuerst die Tabelle erstellen und einige Daten darin füllen.

Beispielcode:

# 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)
);

Diese Abfrage erstellt eine Tabelle namens tb_student, die Sie in der MySQL-Datenbank bestätigen können.

Fügen Sie die sechs Datensätze in die Tabelle mit dem Namen tb_student ein, indem Sie die folgende Syntax der INSERT-Abfrage verwenden.

# 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);

Wählen Sie dann alle Daten aus der Tabelle aus, um sie mithilfe der folgenden Abfrage anzuzeigen.

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

Ihre Tabelle enthält die folgenden Daten. Sie können auch auf Ihrer Seite überprüfen und vergleichen.

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

Nachdem Sie die obige Abfrage ausgeführt haben, erhalten Sie das folgende Ergebnis.

Beachten Sie die obige Ausgabe, und Sie werden sehen, dass alle Datensätze angezeigt werden, die nach dem Registrierungsjahr geordnet sind (siehe die Spalte innerhalb des grünen Kastens). Und die row_number ist auch die gleiche wie erwartet, beginnend bei 1 und fortlaufend steigend bis zum Ende der Tabelle, da wir alle Daten von tb_student lesen.

Verwendung von ROW_NUMBER() in MySQL mit der PARTITION BY-Klausel

Wir werden nur die Funktion ROW_NUMBER() mit der Klausel PARTITION BY verwenden und die Ergebnisse beobachten. Wir werden diese Ausgabe auch mit den Ergebnissen vergleichen, die wir mit ROW_NUMBER() mit der ORDER BY-Klausel erhalten haben.

Beispielcode:

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

Jetzt erhalten Sie die folgenden Ergebnisse.

Sehen Sie sich die Spalte REGISTRATION_YEAR an; Es hat 5 Partitionen (2010, 2011, 2012, 2013 und 2014). Für partition 2010 gibt es in der Tabelle zwei rows, und die Zeilennummern sind korrekt vergeben (siehe nochmal obigen Screenshot). Es gibt nur eine Zeile für partition 2011, 2012, 2013, 2014; deshalb sehen Sie in der Spalte row_numb 1.

Wenn wir die Klausel PARTITION BY verwenden, warum ist die Spalte mit dem Namen REGISTRATION_YEAR dann in aufsteigender Reihenfolge? Weil die Klausel PARTITION BY die Daten innerhalb dieser Partitionen ordnet. Lassen Sie uns einen weiteren Datensatz einfügen, für den der Wert von REGISTRATION_YEAR 2009 wäre, und die Ergebnisse beobachten.

# 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;

Jetzt sehen Sie, dass der aktuelle Datensatz ganz oben steht.

Verwendung von ROW_NUMBER() in MySQL mit PARTITION BY- und ORDER BY-Klausel

Jetzt verwenden wir nur die Funktion ROW_NUMBER() mit den Klauseln PARTITION BY und ORDER BY und sehen, ob sie immer noch die richtigen Zeilennummern liefert.

Beispielcode:

# 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;

Nachdem Sie die obige Abfrage ausgeführt haben, sehen Sie die gleiche Ausgabe wie bei der Verwendung von ROW_NUMBER() mit der PARTITION BY-Klausel. Siehe folgenden Screenshot:

Siehe die Spalte mit gelbem Hintergrund, das haben wir erwartet. Hier verwenden wir die Sitzungsvariable, um Zeilennummern korrekt zuzuweisen.

Replikation von ROW_NUMBER() in MySQL mit Sitzungsvariable

MySQL bietet nicht die korrekte Ranking-Funktionalität, wenn wir gleichzeitig die Klauseln PARTITION BY und ORDER BY verwenden. In diesem Szenario emulieren wir dies mit Session Variable. Sitzungsvariablen sind benutzerdefiniert; Sie können es hier für detaillierte Informationen einsehen.

Beispielcode:

# 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;

Wie Sie unten sehen können, beginnt row_numb bei 1 und erhöht sich fortlaufend.

Wie funktioniert es? Wir haben zuerst eine Sitzungsvariable row_numb mit @-Präfix gesetzt und mit der 0 initialisiert. Dann haben wir die Daten aus der Tabelle ausgewählt, geordnet und gedruckt. (@row_numb:=@row_numb + 1) ist wie das Erhöhen und Aktualisieren des Variablenwerts.

Fazit

In Anbetracht der obigen Diskussion sind wir zu dem Schluss gekommen, dass wir, obwohl wir die Funktion ROW_NUMBER() auch in MySQL verwenden können, wenn wir Version 8.0 oder höher haben, dennoch einige Situationen haben, in denen wir Sitzungsvariablen für Ranking-Zwecke verwenden müssen.