paginering bruges ofte i applikationer, hvor brugeren kan klikke på Forrige / Næste for at navigere på de sider, der udgør resultaterne, eller klikke på et sidetal for at gå direkte til en bestemt side.
Når du kører forespørgsler på serveren, kan du paginere resultaterne ved at brugeOFFSET ogFETCH argumenterne forORDER BY klausulen. Disse argumenter blev introduceret i Server 2012, derfor kan du bruge denne teknik, hvis du har Server 2012 eller højere.
i denne sammenhæng er paginering, hvor du deler forespørgselsresultaterne i mindre bidder, hvor hver del fortsætter, hvor den forrige er færdig. Hvis en forespørgsel f.eks. returnerer 1000 rækker, kan du paginere dem, så de returneres i grupper på 100. En applikation kan overføre sidetal og sidestørrelse til en server, og en Server kan derefter bruge den til kun at returnere dataene for den ønskede side.
eksempel 1 – ingen paginering
lad os først køre en forespørgsel, der returnerer alle rækker i en tabel:
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 eksempel bruger ingen paginering – alle resultater vises.
dette resultatsæt er så lille, at det normalt ikke kræver paginering, men med henblik på denne artikel, lad os paginere det.
eksempel 2-Vis de første 3 resultater
dette eksempel viser de første tre resultater:
SELECT *FROM GenresORDER BY GenreId OFFSET 0 ROWS FETCH NEXT 3 ROWS ONLY;
resultat:
+-----------+---------+| GenreId | Genre ||-----------+---------|| 1 | Rock || 2 | Jazz || 3 | Country |+-----------+---------+
i dette tilfælde angiver jeg, at resultaterne skal starte ved det første resultat og vise de næste tre rækker. Dette gøres ved hjælp af følgende:
-
OFFSET 0 ROWSangiver, at der ikke skal være nogen forskydning (en forskydning på nul). -
FETCH NEXT 3 ROWS ONLYfår de næste tre rækker fra forskydningen. Da jeg specificerede en forskydning på nul, hentes de første tre rækker.
Hvis alt, hvad vi ønskede, var de 3 bedste resultater, kunne vi have opnået det samme resultat ved at brugeTOP klausul i stedet for at specificere offset-og hentningsværdierne. Dette ville dog ikke have tilladt os at gøre den næste del.
eksempel 3-vis de næste 3 resultater
lad os nu vise de næste tre resultater:
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 ændrede, var forskydningen.
offset-og hentningsværdierne kan også være et udtryk, der leveres som en variabel, parameter eller konstant skalær underforespørgsel. Når en underforespørgsel bruges, kan den ikke henvise til nogen kolonner, der er defineret i det ydre forespørgselsomfang (det kan ikke korreleres med den ydre forespørgsel).
følgende eksempler bruger udtryk til at vise to tilgange til paginering af resultaterne.
eksempel 4 – paginering efter rækkenummer
dette eksempel bruger udtryk til at angive det rækkenummer, der skal startes ved.
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 bruger jeg@StartRow int = 1 for at angive, at resultaterne skal starte i første række.
Her er hvad der sker, hvis jeg øger denne værdi til2.
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 |+-----------+---------+
Det starter ved anden række. Ved hjælp af denne metode kan jeg angive den nøjagtige række at starte ved.
eksempel 5 – paginering efter sidetal
dette eksempel er næsten identisk med det foregående eksempel, bortset fra at det giver dig mulighed for at specificere sidetallet i modsætning til rækkenummeret.
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 resultat er det samme. Lad os dog se, hvad der sker, når vi øger @PageNumber til 2 (jeg omdøbte denne variabel for at afspejle dens 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 gang starter resultaterne i fjerde række. Så ved hjælp af denne metode kan du blot passere sidetallet i stedet for rækkenummeret.
eksempel 6 – Paginationssløjfe
for at afslutte, her er et hurtigt eksempel, der løber gennem alle sider og angiver startrækkenummeret for hver iteration:
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 – række vs rækker
Hvis du støder på kode, der bruger ROW i stedet for ROWS, begge argumenter gør det samme. De er synonymer og er fastsat for ANSI Kompatibilitet.
Her er det første eksempel på denne side, men medROWi stedet forROWS.
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 næste
det samme gælder FIRST og NEXT. Disse er synonymer til ANSI-Kompatibilitet.
Her er det forrige eksempel, men medFIRSTi stedet forNEXT.
SELECT *FROM GenresORDER BY GenreId OFFSET 0 ROW FETCH FIRST 3 ROW ONLY;
resultat:
+-----------+---------+| GenreId | Genre ||-----------+---------|| 1 | Rock || 2 | Jazz || 3 | Country |+-----------+---------+