Die Paginierung wird häufig in Anwendungen verwendet, bei denen der Benutzer auf Zurück / Weiter klicken kann, um durch die Seiten zu navigieren, aus denen die Ergebnisse bestehen, oder auf eine Seitenzahl klicken kann, um direkt zu einer bestimmten Seite zu gelangen.
Wenn Sie Abfragen in SQL Server ausführen, können Sie die Ergebnisse paginieren, indem Sie die OFFSET und FETCH Argumente der ORDER BY Klausel verwenden. Diese Argumente wurden in SQL Server 2012 eingeführt, daher können Sie diese Technik verwenden, wenn Sie über SQL Server 2012 oder höher verfügen.
In diesem Zusammenhang teilen Sie die Abfrageergebnisse bei der Paginierung in kleinere Blöcke auf, wobei jeder Block dort fortgesetzt wird, wo der vorherige beendet wurde. Wenn eine Abfrage beispielsweise 1000 Zeilen zurückgibt, können Sie sie so paginieren, dass sie in Gruppen von 100 zurückgegeben werden. Eine Anwendung kann die Seitenzahl und die Seitengröße an SQL Server übergeben, und SQL Server kann sie dann verwenden, um nur die Daten für die angeforderte Seite zurückzugeben.
Beispiel 1 – Keine Paginierung
Lassen Sie uns zunächst eine Abfrage ausführen, die alle Zeilen in einer Tabelle zurückgibt:
SELECT *FROM GenresORDER BY GenreId;
Ergebnis:
+-----------+---------+| GenreId | Genre ||-----------+---------|| 1 | Rock || 2 | Jazz || 3 | Country || 4 | Pop || 5 | Blues || 6 | Hip Hop || 7 | Rap || 8 | Punk |+-----------+---------+
Dieses Beispiel verwendet keine Paginierung – alle Ergebnisse werden angezeigt.
Diese Ergebnismenge ist so klein, dass normalerweise keine Paginierung erforderlich ist, aber für die Zwecke dieses Artikels sollten wir sie paginieren.
Beispiel 2 – Anzeige der ersten 3 Ergebnisse
In diesem Beispiel werden die ersten drei Ergebnisse angezeigt:
SELECT *FROM GenresORDER BY GenreId OFFSET 0 ROWS FETCH NEXT 3 ROWS ONLY;
Ergebnis:
+-----------+---------+| GenreId | Genre ||-----------+---------|| 1 | Rock || 2 | Jazz || 3 | Country |+-----------+---------+
In diesem Fall gebe ich an, dass die Ergebnisse beim ersten Ergebnis beginnen und die nächsten drei Zeilen anzeigen sollen. Dies geschieht mit folgendem:
-
OFFSET 0 ROWSgibt an, dass kein Offset (ein Offset von Null) vorhanden sein soll. -
FETCH NEXT 3 ROWS ONLYermittelt die nächsten drei Zeilen aus dem Offset. Da ich einen Versatz von Null angegeben habe, werden die ersten drei Zeilen abgerufen.
Wenn wir nur die Top-3-Ergebnisse wollten, hätten wir das gleiche Ergebnis erzielen können, indem wir die TOP Klausel verwendet hätten, anstatt die Offset- und fetch-Werte anzugeben. Dies hätte uns jedoch nicht erlaubt, den nächsten Teil zu machen.
Beispiel 3 – Anzeige der nächsten 3 Ergebnisse
Lassen Sie uns nun die nächsten drei Ergebnisse anzeigen:
SELECT *FROM GenresORDER BY GenreId OFFSET 3 ROWS FETCH NEXT 3 ROWS ONLY;
Ergebnis:
+-----------+---------+| GenreId | Genre ||-----------+---------|| 4 | Pop || 5 | Blues || 6 | Hip Hop |+-----------+---------+
Das einzige, was ich geändert habe, war der Offset.
Die Offset- und Abrufwerte können auch ein Ausdruck sein, der als Variable, Parameter oder konstante skalare Unterabfrage bereitgestellt wird. Wenn eine Unterabfrage verwendet wird, kann sie nicht auf Spalten verweisen, die im Bereich der äußeren Abfrage definiert sind (sie kann nicht mit der äußeren Abfrage korreliert werden).
Die folgenden Beispiele verwenden Ausdrücke, um zwei Ansätze zum Paginieren der Ergebnisse zu zeigen.
Beispiel 4 – Paginierung nach Zeilennummer
In diesem Beispiel werden Ausdrücke verwendet, um die Zeilennummer anzugeben, mit der begonnen werden soll.
DECLARE @StartRow int = 1, @RowsPerPage int = 3; SELECT * FROM GenresORDER BY GenreId ASC OFFSET @StartRow - 1 ROWS FETCH NEXT @RowsPerPage ROWS ONLY;
Ergebnis:
+-----------+---------+| GenreId | Genre ||-----------+---------|| 1 | Rock || 2 | Jazz || 3 | Country |+-----------+---------+
Hier verwende ich @StartRow int = 1, um anzugeben, dass die Ergebnisse in der ersten Zeile beginnen sollen.
Folgendes passiert, wenn ich diesen Wert auf 2 erhöhe.
DECLARE @StartRow int = 2, @RowsPerPage int = 3; SELECT * FROM GenresORDER BY GenreId ASC OFFSET @StartRow - 1 ROWS FETCH NEXT @RowsPerPage ROWS ONLY;
Ergebnis:
+-----------+---------+| GenreId | Genre ||-----------+---------|| 2 | Jazz || 3 | Country || 4 | Pop |+-----------+---------+
Es beginnt in der zweiten Zeile. Mit dieser Methode kann ich die genaue Zeile angeben, in der ich beginnen soll.
Beispiel 5 – Paginierung nach Seitenzahl
Dieses Beispiel ist fast identisch mit dem vorherigen Beispiel, außer dass Sie die Seitenzahl im Gegensatz zur Zeilennummer angeben können.
DECLARE @PageNumber int = 1, @RowsPerPage int = 3; SELECT * FROM GenresORDER BY GenreId ASC OFFSET (@PageNumber - 1) * @RowsPerPage ROWS FETCH NEXT @RowsPerPage ROWS ONLY;
Ergebnis:
+-----------+---------+| GenreId | Genre ||-----------+---------|| 1 | Rock || 2 | Jazz || 3 | Country |+-----------+---------+
Das erste Ergebnis ist also dasselbe. Mal sehen, was passiert, wenn wir @PageNumber auf 2 erhöhen (ich habe diese Variable umbenannt, um ihren neuen Zweck widerzuspiegeln).
DECLARE @PageNumber int = 2, @RowsPerPage int = 3; SELECT * FROM GenresORDER BY GenreId ASC OFFSET (@PageNumber - 1) * @RowsPerPage ROWS FETCH NEXT @RowsPerPage ROWS ONLY;
Ergebnis:
+-----------+---------+| GenreId | Genre ||-----------+---------|| 4 | Pop || 5 | Blues || 6 | Hip Hop |+-----------+---------+
Dieses Mal beginnen die Ergebnisse in der vierten Zeile. Mit dieser Methode können Sie also einfach die Seitenzahl anstelle der Zeilennummer übergeben.
Beispiel 6 – Paginierungsschleife
Zum Abschluss finden Sie hier ein kurzes Beispiel, das alle Seiten durchläuft und die Startzeilennummer für jede Iteration angibt:
DECLARE @StartRow int = 1, @RowsPerPage int = 3;WHILE (SELECT COUNT(*) FROM Genres) >= @StartRow BEGIN SELECT * FROM Genres ORDER BY GenreId ASC OFFSET @StartRow - 1 ROWS FETCH NEXT @RowsPerPage ROWS ONLY;SET @StartRow = @StartRow + @RowsPerPage; CONTINUEEND;
Ergebnis:
+-----------+---------+| GenreId | Genre ||-----------+---------|| 1 | Rock || 2 | Jazz || 3 | Country |+-----------+---------+(3 rows affected)+-----------+---------+| GenreId | Genre ||-----------+---------|| 4 | Pop || 5 | Blues || 6 | Hip Hop |+-----------+---------+(3 rows affected)+-----------+---------+| GenreId | Genre ||-----------+---------|| 7 | Rap || 8 | Punk |+-----------+---------+(2 rows affected)
Beispiel 7 – ZEILE gegen ZEILE
Wenn Sie auf Code stoßen, derROW anstelle von ROWS machen beide Argumente dasselbe. Sie sind Synonyme und dienen der ANSI-Kompatibilität.
Hier ist das erste Beispiel auf dieser Seite, aber mit ROW anstelle von ROWS.
SELECT *FROM GenresORDER BY GenreId OFFSET 0 ROW FETCH NEXT 3 ROW ONLY;
Ergebnis:
+-----------+---------+| GenreId | Genre ||-----------+---------|| 1 | Rock || 2 | Jazz || 3 | Country |+-----------+---------+
Beispiel 8 – FIRST vs NEXT
Das gleiche gilt für FIRST und NEXT. Dies sind Synonyme, die für die ANSI-Kompatibilität bereitgestellt werden.
Hier ist das vorherige Beispiel, aber mit FIRST anstelle von NEXT.
SELECT *FROM GenresORDER BY GenreId OFFSET 0 ROW FETCH FIRST 3 ROW ONLY;
Ergebnis:
+-----------+---------+| GenreId | Genre ||-----------+---------|| 1 | Rock || 2 | Jazz || 3 | Country |+-----------+---------+