paginering används ofta i applikationer där användaren kan klicka på Föregående / Nästa för att navigera på sidorna som utgör resultaten, eller klicka på ett sidnummer för att gå direkt till en viss sida.
När du kör frågor i SQL Server kan du paginera resultaten med hjälp avOFFSETochFETCHargumenten iORDER BY klausulen. Dessa argument introducerades i SQL Server 2012, därför kan du använda den här tekniken om du har SQL Server 2012 eller senare.
i det här sammanhanget är paginering där du delar upp frågeresultaten i mindre bitar, varje bit fortsätter där den föregående slutade. Om en fråga till exempel returnerar 1000 rader kan du paginera dem så att de returneras i grupper om 100. Ett program kan skicka sidnummer och sidstorlek till SQL Server, och SQL Server kan sedan använda den för att returnera bara data för den begärda sidan.
exempel 1-ingen paginering
Låt oss först köra en fråga som returnerar alla 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 |+-----------+---------+
detta exempel använder ingen paginering – alla resultat visas.
denna resultatuppsättning är så liten att den normalt inte skulle kräva paginering, men för den här artikeln, låt oss paginera den.
exempel 2-Visa De första 3 resultaten
detta exempel visar de tre första resultaten:
SELECT *FROM GenresORDER BY GenreId OFFSET 0 ROWS FETCH NEXT 3 ROWS ONLY;
resultat:
+-----------+---------+| GenreId | Genre ||-----------+---------|| 1 | Rock || 2 | Jazz || 3 | Country |+-----------+---------+
i det här fallet anger jag att resultaten ska börja vid det första resultatet och visa de kommande tre raderna. Detta görs med följande:
-
OFFSET 0 ROWSanger att det inte ska finnas någon förskjutning (en förskjutning på noll). -
FETCH NEXT 3 ROWS ONLYfår de kommande tre raderna från förskjutningen. Eftersom jag angav en förskjutning på noll, hämtas de tre första raderna.
om allt vi ville ha var de 3 bästa resultaten kunde vi ha uppnått samma resultat genom att använda TOP – klausulen istället för att ange offset-och hämtningsvärdena. Detta skulle dock inte ha tillåtit oss att göra nästa del.
exempel 3-Visa nästa 3 resultat
låt oss nu visa nästa tre resultat:
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 enda jag ändrade var förskjutningen.
offset-och hämtningsvärdena kan också vara ett uttryck som tillhandahålls som en variabel, parameter eller konstant skalär underfråga. När en underfråga används kan den inte referera till några kolumner som definierats i det yttre frågeområdet (det kan inte korreleras med den yttre frågan).
följande exempel använder uttryck för att visa två metoder för paginering av resultaten.
exempel 4-paginering efter radnummer
detta exempel använder uttryck för att ange radnumret som ska börja 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 |+-----------+---------+
här använder jag @StartRow int = 1 för att ange att resultaten ska börja på första raden.
Här är vad som händer om jag ökar det värdet till 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 |+-----------+---------+
det börjar på den andra raden. Med den här metoden kan jag ange den exakta raden att börja på.
exempel 5-paginering efter sidnummer
det här exemplet är nästan identiskt med föregående exempel, förutom att det låter dig ange sidnummer, i motsats till radnumret.
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örsta resultatet är detsamma. Låt oss dock se vad som händer när vi ökar @PageNumber till 2 (jag bytte namn på denna variabel för att återspegla dess nya syfte).
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 |+-----------+---------+
den här gången börjar resultaten på fjärde raden. Så med den här metoden kan du helt enkelt skicka sidnumret snarare än radnumret.
exempel 6 – Pagineringsslinga
för att avsluta, här är ett snabbt exempel som slingrar igenom alla sidor och anger startradnumret för varje 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)
exempel 7 – rad vs rader
om du stöter på kod som använder ROW istället för ROWS gör båda argumenten samma sak. De är synonymer och tillhandahålls för ANSI-kompatibilitet.
här är det första exemplet på den här sidan, men med ROW istället för ROWS.
SELECT *FROM GenresORDER BY GenreId OFFSET 0 ROW FETCH NEXT 3 ROW ONLY;
resultat:
+-----------+---------+| GenreId | Genre ||-----------+---------|| 1 | Rock || 2 | Jazz || 3 | Country |+-----------+---------+
exempel 8 – första vs nästa
detsamma gäller FIRST och NEXT. Dessa är synonymer som tillhandahålls för ANSI-kompatibilitet.
här är föregående exempel men med FIRST istället för NEXT.
SELECT *FROM GenresORDER BY GenreId OFFSET 0 ROW FETCH FIRST 3 ROW ONLY;
resultat:
+-----------+---------+| GenreId | Genre ||-----------+---------|| 1 | Rock || 2 | Jazz || 3 | Country |+-----------+---------+