In diesem Artikel untersuchen wir die OFFSET- und FETCH-Klauseln. OFFSET und FETCH werden in Verbindung mit der SELECT-Anweisung ORDER BY-Klausel verwendet, um ein Mittel zum Abrufen eines Datensatzbereichs bereitzustellen. Die zurückzugebende Startzeile wird durch den OFFSET-Wert und die maximale Anzahl von Zeilen bestimmt, die ab diesem Zeitpunkt von FETCH zurückgegeben werden sollen.

Alle Beispiele für diese Lektion basieren auf Microsoft SQL Server Management Studio und der AdventureWorks2012-Datenbank. Sie können mit diesen kostenlosen Tools beginnen, indem Sie meinen Leitfaden Erste Schritte mit SQL Server verwenden.

Verwenden von OFFSET und FETCH mit der ORDER BY-Klausel

Die Rückgabe von Zeilen aus einer SQL-Anweisung kann eine Alles- oder-Nichts-Angelegenheit sein. In vielen Fällen ist die Anzahl der zurückgegebenen Zeilen sehr groß und dies kann zu Problemen führen, wenn Sie nur einen Teil der Ergebnismenge benötigen.

Wenn Ergebnisse mit der ORDER BY-Klausel sortiert werden, kommen einige Optionen ins Spiel, um die Anzahl der zurückgegebenen Zeilen zu begrenzen:

1. Sie können TOP verwenden, um eine bestimmte Anzahl von Zeilen zurückzugeben.
2. Sie können OFFSET und FETCH verwenden.

In diesem Artikel erfahren Sie mehr über OFFSET und FETCH. Um mehr darüber zu erfahren, lesen Sie den Artikel Erste Schritte mit SQL Server: 2. Sortieren Sie Ihre Abfrageergebnisse.

OFFSET

Das Argument OFFSET wird verwendet, um den Ausgangspunkt für die Rückgabe von Zeilen aus einem Ergebnis zu identifizieren. OFFESET wird als Argument bezeichnet, da es technisch Teil der ORDER BY-Klausel ist. Der OFFSET ist die Anzahl der Zeilen, die übersprungen werden müssen, bevor sie in das Ergebnis aufgenommen werden.

Die allgemeine Form für das Argument OFFSET ist:

SELECT columnsFROM tableORDER BY columns OFFSET rows-to-skip ROWS

Wobei die zu überspringenden Zeilen ein Wert größer oder gleich Null sind.

Um beispielsweise alle bis auf die ersten 10 Mitarbeiter, sortiert nach Anstellungsdatum, anzuzeigen, können Sie schreiben

SELECT NationalIDNumber, JobTitle, HireDateFROM HumanResources.EmployeeORDER BY HireDate OFFSET 10 ROWS

Hier sind einige Dinge zu beachten OFFSET

• OFFSET ist Teil der ORDER BY Klausel. Es kann nicht alleine verwendet werden.
• OFFSET-Werte müssen Null oder größer sein. Eine negative Zahl führt zu einem Fehler.
• Wenn OFFSET 0 ist, werden keine Zeilen übersprungen.
• Wenn OFFSET größer als die Anzahl der Zeilen in den geordneten Ergebnissen ist, werden keine Zeilen zurückgegeben.

FETCH

Das FETCH-Argument wird verwendet, um eine festgelegte Anzahl von Zeilen zurückzugeben. FETCH kann nicht alleine verwendet werden, es wird in Verbindung mit OFFSET verwendet.

Wenn wir mit unserem Beispiel fortfahren, können wir die 11. bis 15. Mitarbeiter zeigen, die mit dieser Anweisung eingestellt wurden

SELECT NationalIDNumber, JobTitle, HireDateFROM HumanResources.EmployeeORDER BY HireDate OFFSET 10 ROWS FETCH NEXT 5 ROWS ONLY

Im folgenden Diagramm sehen Sie OFFSET und FETCH bei der Arbeit. OFFSET wird verwendet, um die ersten 10 Zeilen zu überspringen, und FETCH wird dann verwendet, um die nächsten 5 anzuzeigen.

Die Kombination von OFFSET und FETCH macht es einfach, ein „gleitendes“ Fenster von Zeilen abzurufen. Der Anfang des Fensters wird durch OFFSET und die Höhe durch FETCH bestimmt.

Verwendungen für OFFSET und FETCH

Paging

Eine der beliebtesten Verwendungen für OFFSET und FETCH ist Paging. Zweifellos haben Sie eine Website besucht, auf der eine Liste der Elemente angezeigt wird, und unten finden Sie eine Liste der Seitenzahlen oder eine Schaltfläche Weiter.

Wir verwenden Paging die ganze Zeit im Web. Das beliebteste Beispiel, das ich mir vorstellen kann, ist Google:

Die Zahlen unter Google stellen Seitenzahlen dar. Ein Teil der Suchergebnisse wird mit jedem Klick zurückgegeben.Angenommen, wir haben eine Webseite, auf der Mitarbeiter nach Einstellungsdatum angezeigt werden. Wenn wir 20 Mitarbeiter auf einer Seite anzeigen möchten und die 3. Seite (Mitarbeiter 21-30) anzeigen, können wir die folgende Abfrage verwenden:

SELECT NationalIDNumber, JobTitle, HireDateFROM HumanResources.EmployeeORDER BY HireDate OFFSET 20 ROWS FETCH NEXT 10 ROWS ONLY

Diese Abfrage weist SQL an:

1. Wählen Sie die Mitarbeiterinformationen aus
2. Ordnen Sie die Informationen nach HireDate
3. Überspringen Sie 20 Zeilen und starten Sie die Anzeige der Ergebnisse ab dem 21.
4. Zeigen Sie die nächsten 10 Ergebniszeilen an.

Abrufen der Top-Datensätze

Wenn Sie die Top-Ten-Zeilen in einer Abfrage abrufen möchten, können Sie dies tun, indem Sie OFFSET auf 0 setzen. Denken Sie daran, dass der OFFSET die Anzahl der zu überspringenden Zeilen angibt. Indem wir es auf Null setzen, weisen wir SQL an, in der ersten Zeile zu beginnen.

Sobald OFFSET gesetzt ist, besteht die nächste Aufgabe darin, FETCH zu setzen. Da wir nach den Top Ten suchen, setzen wir FETCH NEBEN 10.

SELECT NationalIDNumber, JobTitle, HireDateFROM HumanResources.EmployeeORDER BY HireDate OFFSET 0 ROWS FETCH NEXT 10 ROWS ONLY

Wenn Sie mit der TOP-Klausel vertraut sind, haben Sie vielleicht bemerkt, dass es Ähnlichkeiten zwischen diesen beiden Methoden gibt. Zum Beispiel würde das obige Beispiel mit TOP so aussehen

SELECT TOP 10 NationalIDNumber, JobTitle, HireDateFROM HumanResources.EmployeeORDER BY HireDate

Es gibt Aspekte zu TOP , die nicht für OFFSET und FETCH gelten; wie in Anweisungen ohne ORDER BY erlaubt, aber wie Sie in diesem Beispiel sehen können, geben sie äquivalente Ergebnisse zurück.

Abrufen der unteren Datensätze

Um die unteren Datensätze in einer Ergebnismenge abzurufen, gibt es zwei Möglichkeiten. Die erste besteht darin, das Ergebnis in absteigender Reihenfolge im Gegensatz zu aufsteigender Reihenfolge zu ordnen. Dies platziert die unteren Ergebnisse nach oben. Dann können Sie fetch wie gewohnt verwenden.

SELECT NationalIDNumber, JobTitle, HireDateFROM HumanResources.EmployeeORDER BY HireDate DESC OFFSET 0 ROWS FETCH NEXT 10 ROWS ONLY

Wenn Sie die Reihenfolge der Ergebnisse nicht ändern möchten, können Sie eine Unterabfrage verwenden, um die Anzahl der Datensätze abzurufen. Dies kann dann verwendet werden, um den Offset entsprechend einzustellen.

SELECT NationalIDNumber, JobTitle, HireDateFROM HumanResources.EmployeeORDER BY HireDate OFFSET (SELECT COUNT(*) FROM HumanResources.Employee)-10 ROWS FETCH NEXT 10 ROWS ONLY

Bei dieser Methode besteht eine gewisse Gefahr, da der Ausdruck zur Berechnung des OFFSETS zu einem Wert unter Null führen kann. In unserem Beispiel könnte dies passieren, wenn die Gesamtzahl der Zeilen weniger als zehn beträgt. Um sich gegen diese Bedingung zu verteidigen, möchten Sie eine CASE-Anweisung in Ihre Logik integrieren, um diese Bedingung zu überprüfen.

Sampling-Daten in der Mitte einer Ergebnismenge

Eine nette Funktion von FETCH und OFFSET Sie können SQL schreiben, um Daten aus der Mitte des Ergebnisses zu samplen oder zu testen. Dies ist sehr praktisch, wenn Sie einen Blick in die Mitte einer Ergebnismenge werfen müssen, die möglicherweise Millionen von Zeilen enthält, ohne alle Zeilen bis zu dem Punkt anzuzeigen, den Sie überprüfen möchten.

Um in der Mitte zu sampeln, würden Sie die gleiche Logik verwenden wie für das Paging. Natürlich kann die Anzahl der Zeilen, die Sie in diesem Fall abrufen, viel größer sein.

Auswirkung von ORDER BY auf OFFSET und FETCH

OFFSET und FETCH funktionieren nur in Verbindung mit einer ORDER BY-Klausel. Tatsächlich ruft SQL zuerst die von Ihnen angegebenen Daten ab, z. B. Spalten, und ordnet die Daten dann in aufsteigender oder absteigender Reihenfolge an.

Erst nach Abschluss dieses Schritts werden Zeilen übersprungen und die Ergebnisse erzeugt.

Grenzfälle

Da einige Werte zu einem Fehler führen können, wie z. B. ein negativer OFFSET, lassen Sie uns verschiedene Kombinationen von Werten untersuchen, die die Anzahl der Zeilen in einer Tabelle überschreiten können, um zu verstehen, welche Werte sicher zu verwenden sind und welche Werte einen SQL-Fehler auslösen würden.

Wir werden unsere Beispiele auf die Humanressourcen stützen.Employee-Tabelle, die 290 Zeilen enthält.

Der einzige Fall, der zu einem Fehler führt, ist, wenn der OFFSET negativ ist. Es gibt Fälle, die keine Zeilen zurückgeben oder möglicherweise weniger Zeilen als Sie denken, aber diese Fälle werfen keine Fehler aus. In den meisten Fällen treten diese Situationen auf, wenn entweder der OFFSETWERT größer als die Anzahl der Zeilen im Ergebnis ist (alle Zeilen werden übersprungen) oder wenn Sie versuchen, „past“ und dann das Ende des Ergebnisses abzurufen.