paginering wordt vaak gebruikt in toepassingen waar de gebruiker op Vorige/Volgende kan klikken om door de pagina ‘ s te navigeren waaruit de resultaten bestaan, of op een paginanummer kan klikken om direct naar een specifieke pagina te gaan.
bij het uitvoeren van queries in SQL Server, kunt u de resultaten pagineren met behulp van de OFFSET en FETCH argumenten van de ORDER BY clausule. Deze argumenten werden geïntroduceerd in SQL Server 2012, daarom kun je deze techniek gebruiken als je SQL Server 2012 of hoger hebt.
in deze context is paginering waar u de resultaten van de query deelt in kleinere brokken, elk brokje gaat verder waar de vorige is voltooid. Als een query bijvoorbeeld 1000 rijen retourneert, kunt u ze pagineren zodat ze worden geretourneerd in groepen van 100. Een toepassing kan het paginanummer en de paginagrootte doorgeven aan SQL Server, en SQL Server kan het vervolgens gebruiken om alleen de gegevens voor de gevraagde pagina terug te geven.
Voorbeeld 1 – Geen paginering
laten we eerst een query uitvoeren die alle rijen in een tabel retourneert:
SELECT *FROM GenresORDER BY GenreId;
resultaat:
+-----------+---------+| GenreId | Genre ||-----------+---------|| 1 | Rock || 2 | Jazz || 3 | Country || 4 | Pop || 5 | Blues || 6 | Hip Hop || 7 | Rap || 8 | Punk |+-----------+---------+
dit voorbeeld gebruikt geen paginering – alle resultaten worden weergegeven.
deze resultaatset is zo klein dat het normaal gesproken geen paginering vereist, maar voor de doeleinden van dit artikel, laten we het pagineren.
Voorbeeld 2-Toon de eerste 3 Resultaten
dit voorbeeld toont de eerste drie resultaten:
SELECT *FROM GenresORDER BY GenreId OFFSET 0 ROWS FETCH NEXT 3 ROWS ONLY;
resultaat:
+-----------+---------+| GenreId | Genre ||-----------+---------|| 1 | Rock || 2 | Jazz || 3 | Country |+-----------+---------+
In dit geval geef ik aan dat de resultaten moeten beginnen bij het eerste resultaat en de volgende drie rijen weergeven. Dit wordt gedaan met behulp van de volgende:
-
OFFSET 0 ROWSgeeft aan dat er geen offset mag zijn (een offset van nul). -
FETCH NEXT 3 ROWS ONLYkrijgt de volgende drie rijen van de offset. Aangezien ik een offset van nul heb opgegeven, worden de eerste drie rijen opgehaald.
als we alleen de top 3-Resultaten wilden, hadden we hetzelfde resultaat kunnen bereiken door de TOP – clausule te gebruiken in plaats van de offset-en fetch-waarden op te geven. Dit zou ons echter niet hebben toegestaan om het volgende deel te doen.
Voorbeeld 3-Toon de volgende 3 Resultaten
laten we nu de volgende drie resultaten tonen:
SELECT *FROM GenresORDER BY GenreId OFFSET 3 ROWS FETCH NEXT 3 ROWS ONLY;
resultaat:
+-----------+---------+| GenreId | Genre ||-----------+---------|| 4 | Pop || 5 | Blues || 6 | Hip Hop |+-----------+---------+
dus het enige wat ik veranderde was de offset.
de offset-en fetching-waarden kunnen ook een expressie zijn die wordt geleverd als een variabele, parameter of een constante scalaire subquery. Wanneer een subquery wordt gebruikt, kan het niet verwijzen naar kolommen gedefinieerd in de outer query scope (Het kan niet worden gecorreleerd met de outer query).
de volgende voorbeelden gebruiken expressies om twee benaderingen te tonen voor het pagineren van de resultaten.
Voorbeeld 4-paginering op rijnummer
dit voorbeeld gebruikt expressies om het rijnummer te specificeren waarmee moet worden begonnen.
DECLARE @StartRow int = 1, @RowsPerPage int = 3; SELECT * FROM GenresORDER BY GenreId ASC OFFSET @StartRow - 1 ROWS FETCH NEXT @RowsPerPage ROWS ONLY;
resultaat:
+-----------+---------+| GenreId | Genre ||-----------+---------|| 1 | Rock || 2 | Jazz || 3 | Country |+-----------+---------+
Hier gebruik ik @StartRow int = 1 om aan te geven dat de resultaten op de eerste rij moeten beginnen.
Dit is wat er gebeurt als ik die waarde verhoog naar 2.
DECLARE @StartRow int = 2, @RowsPerPage int = 3; SELECT * FROM GenresORDER BY GenreId ASC OFFSET @StartRow - 1 ROWS FETCH NEXT @RowsPerPage ROWS ONLY;
resultaat:
+-----------+---------+| GenreId | Genre ||-----------+---------|| 2 | Jazz || 3 | Country || 4 | Pop |+-----------+---------+
Het begint bij de tweede rij. Met deze methode kan ik de exacte rij opgeven om mee te beginnen.
Voorbeeld 5-paginering op paginanummer
dit voorbeeld is bijna identiek aan het vorige voorbeeld, behalve dat het u toestaat om het paginanummer te specificeren, in tegenstelling tot het rijnummer.
DECLARE @PageNumber int = 1, @RowsPerPage int = 3; SELECT * FROM GenresORDER BY GenreId ASC OFFSET (@PageNumber - 1) * @RowsPerPage ROWS FETCH NEXT @RowsPerPage ROWS ONLY;
resultaat:
+-----------+---------+| GenreId | Genre ||-----------+---------|| 1 | Rock || 2 | Jazz || 3 | Country |+-----------+---------+
dus het eerste resultaat is hetzelfde. Laten we echter eens kijken wat er gebeurt als we @PageNumber verhogen naar 2 (ik hernoemde deze variabele om zijn nieuwe doel weer te geven).
DECLARE @PageNumber int = 2, @RowsPerPage int = 3; SELECT * FROM GenresORDER BY GenreId ASC OFFSET (@PageNumber - 1) * @RowsPerPage ROWS FETCH NEXT @RowsPerPage ROWS ONLY;
resultaat:
+-----------+---------+| GenreId | Genre ||-----------+---------|| 4 | Pop || 5 | Blues || 6 | Hip Hop |+-----------+---------+
Deze keer beginnen de resultaten bij de vierde rij. Dus met behulp van deze methode kunt u gewoon het paginanummer doorgeven in plaats van het rijnummer.
Voorbeeld 6 – Pagination Loop
om af Te ronden, hier een kort voorbeeld, dat loopt door alle pagina ‘ s en geeft het begin rij nummer voor elke iteratie:
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;
Resultaat:
+-----------+---------+| 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)
Voorbeeld 7 – RIJ vs RIJEN
Als u problemen met de code die gebruikt ROW in plaats van ROWS, beide argumenten hetzelfde doen. Ze zijn synoniemen en zijn voorzien voor ANSI Compatibiliteit.
Hier is het eerste voorbeeld op deze pagina, maar met ROW in plaats van ROWS.
SELECT *FROM GenresORDER BY GenreId OFFSET 0 ROW FETCH NEXT 3 ROW ONLY;
resultaat:
+-----------+---------+| GenreId | Genre ||-----------+---------|| 1 | Rock || 2 | Jazz || 3 | Country |+-----------+---------+
Voorbeeld 8 – eerste vs volgende
hetzelfde geldt voor FIRST en NEXT. Dit zijn Synoniemen voorzien voor ANSI Compatibiliteit.
Hier is het vorige voorbeeld maar met FIRST in plaats van NEXT.
SELECT *FROM GenresORDER BY GenreId OFFSET 0 ROW FETCH FIRST 3 ROW ONLY;
resultaat:
+-----------+---------+| GenreId | Genre ||-----------+---------|| 1 | Rock || 2 | Jazz || 3 | Country |+-----------+---------+