Paginering brukes ofte i applikasjoner der brukeren kan klikke Forrige / Neste for å navigere på sidene som utgjør resultatene, eller klikke på et sidetall for å gå direkte til en bestemt side.
når du kjører spørringer I SQL Server, kan du paginere resultatene ved hjelp avOFFSET ogFETCH argumentene iORDER BY – setningsdelen. Disse argumentene ble introdusert I SQL Server 2012, derfor kan du bruke denne teknikken hvis DU HAR SQL Server 2012 eller høyere.
i denne sammenheng er paginering der du deler spørringsresultatene i mindre biter, hver del fortsetter der den forrige er ferdig. Hvis en spørring for eksempel returnerer 1000 rader, kan du paginere dem slik at de returneres i grupper på 100. Et program kan sende sidetallet og sidestørrelsen TIL SQL Server, OG SQL Server kan deretter bruke den til å returnere bare dataene for den forespurte siden.
Eksempel 1 – Ingen Paginering
la Oss først kjøre en spørring som returnerer alle rader i en tabell:
SELECT *FROM GenresORDER BY GenreId;
Resultat:
+-----------+---------+| GenreId | Genre ||-----------+---------|| 1 | Rock || 2 | Jazz || 3 | Country || 4 | Pop || 5 | Blues || 6 | Hip Hop || 7 | Rap || 8 | Punk |+-----------+---------+
dette eksemplet bruker ingen paginering – alle resultater vises –
dette resultatsettet er så lite at det normalt ikke ville kreve paginering, men i forbindelse med denne artikkelen, la oss paginere den.
Eksempel 2-Vis De Første 3 Resultatene
dette eksemplet viser de tre første resultatene:
SELECT *FROM GenresORDER BY GenreId OFFSET 0 ROWS FETCH NEXT 3 ROWS ONLY;
Resultat:
+-----------+---------+| GenreId | Genre ||-----------+---------|| 1 | Rock || 2 | Jazz || 3 | Country |+-----------+---------+
I dette tilfellet angir jeg at resultatene skal starte ved første resultat og vise de neste tre radene. Dette gjøres ved å bruke følgende:
-
OFFSET 0 ROWSangir at det ikke skal være noen forskyvning(en forskyvning på null). -
FETCH NEXT 3 ROWS ONLYfår de neste tre radene fra forskyvningen. Siden jeg angav en forskyvning på null, hentes de tre første radene.
hvis alt vi ønsket var topp 3-resultatene, kunne vi ha oppnådd det samme resultatet ved å brukeTOP – klausulen i stedet for å angi offset-og henteverdiene. Dette ville imidlertid ikke ha tillatt oss å gjøre neste del.
Eksempel 3-Vis De Neste 3 Resultatene
la Oss nå vise de neste tre resultatene:
SELECT *FROM GenresORDER BY GenreId OFFSET 3 ROWS FETCH NEXT 3 ROWS ONLY;
Resultat:
+-----------+---------+| GenreId | Genre ||-----------+---------|| 4 | Pop || 5 | Blues || 6 | Hip Hop |+-----------+---------+
så det eneste jeg endret var forskyvningen.
offset-og henteverdiene kan også være et uttrykk gitt som en variabel, parameter eller konstant skalar subquery. Når en delspørring brukes, kan den ikke referere til noen kolonner som er definert i det ytre spørringsområdet (den kan ikke korreleres med den ytre spørringen).
følgende eksempler bruker uttrykk for å vise to tilnærminger til paginering av resultatene.
Eksempel 4-Paginering med Radnummer
dette eksemplet bruker uttrykk for å angi radnummeret som skal starte på.
DECLARE @StartRow int = 1, @RowsPerPage int = 3; SELECT * FROM GenresORDER BY GenreId ASC OFFSET @StartRow - 1 ROWS FETCH NEXT @RowsPerPage ROWS ONLY;
Resultat:
+-----------+---------+| GenreId | Genre ||-----------+---------|| 1 | Rock || 2 | Jazz || 3 | Country |+-----------+---------+
her bruker jeg @StartRow int = 1 for å angi at resultatene skal starte på første rad.
her er hva som skjer hvis jeg øker den verdien til 2.
DECLARE @StartRow int = 2, @RowsPerPage int = 3; SELECT * FROM GenresORDER BY GenreId ASC OFFSET @StartRow - 1 ROWS FETCH NEXT @RowsPerPage ROWS ONLY;
Resultat:
+-----------+---------+| GenreId | Genre ||-----------+---------|| 2 | Jazz || 3 | Country || 4 | Pop |+-----------+---------+
den starter på den andre raden. Ved hjelp av denne metoden kan jeg angi den eksakte raden for å starte på.
Eksempel 5-Paginering etter Sidetall
dette eksemplet er nesten identisk med det forrige eksemplet, bortsett fra at det lar deg angi sidetallet, i motsetning til radnummeret.
DECLARE @PageNumber int = 1, @RowsPerPage int = 3; SELECT * FROM GenresORDER BY GenreId ASC OFFSET (@PageNumber - 1) * @RowsPerPage ROWS FETCH NEXT @RowsPerPage ROWS ONLY;
Resultat:
+-----------+---------+| GenreId | Genre ||-----------+---------|| 1 | Rock || 2 | Jazz || 3 | Country |+-----------+---------+
så det første resultatet er det samme. Men la oss se hva som skjer når vi øker @PageNumber til 2 (jeg omdøpt denne variabelen for å gjenspeile sitt nye formål).
DECLARE @PageNumber int = 2, @RowsPerPage int = 3; SELECT * FROM GenresORDER BY GenreId ASC OFFSET (@PageNumber - 1) * @RowsPerPage ROWS FETCH NEXT @RowsPerPage ROWS ONLY;
Resultat:
+-----------+---------+| GenreId | Genre ||-----------+---------|| 4 | Pop || 5 | Blues || 6 | Hip Hop |+-----------+---------+
denne gangen starter resultatene på fjerde rad. Så ved hjelp av denne metoden kan du bare passere sidetallet i stedet for radnummeret.
Eksempel 6 – Paginering Loop
for å avslutte, her er et raskt eksempel som går gjennom alle sider og angir start radnummer for hver iterasjon:
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;
Resultat:
+-----------+---------+| 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)
Eksempel 7 – RAD vs RADER
hvis du møter kode som bruker ROW i stedet for ROWS, gjør begge argumentene det samme. De er synonymer og er gitt FOR ANSI kompatibilitet.
her er det første eksemplet på denne siden, men med ROW i stedet for ROWS.
SELECT *FROM GenresORDER BY GenreId OFFSET 0 ROW FETCH NEXT 3 ROW ONLY;
Resultat:
+-----------+---------+| GenreId | Genre ||-----------+---------|| 1 | Rock || 2 | Jazz || 3 | Country |+-----------+---------+
Eksempel 8 – FØRSTE vs NESTE
det samme gjelder FIRST og NEXT. Dette er synonymer gitt FOR ANSI-kompatibilitet.
her er det forrige eksemplet, men med FIRSTi stedet for NEXT.
SELECT *FROM GenresORDER BY GenreId OFFSET 0 ROW FETCH FIRST 3 ROW ONLY;
Resultat:
+-----------+---------+| GenreId | Genre ||-----------+---------|| 1 | Rock || 2 | Jazz || 3 | Country |+-----------+---------+