paginarea este adesea folosită în aplicații în care utilizatorul poate face clic pe Anterior / Următor pentru a naviga în paginile care alcătuiesc rezultatele sau face clic pe un număr de pagină pentru a merge direct la o anumită pagină.
când executați interogări în SQL Server, Puteți pagina rezultatele utilizândOFFSET șiFETCH argumentele clauzeiORDER BY. Aceste argumente au fost introduse în SQL Server 2012, prin urmare, puteți utiliza această tehnică dacă aveți SQL Server 2012 sau mai mare.
în acest context, paginarea este locul în care împărțiți rezultatele interogării în bucăți mai mici, fiecare bucată continuând acolo unde s-a terminat precedentul. De exemplu, dacă o interogare returnează 1000 de rânduri, le puteți Pagina astfel încât să fie returnate în grupuri de 100. O aplicație poate trece numărul paginii și dimensiunea paginii la SQL Server, iar SQL Server îl poate folosi apoi pentru a returna doar datele pentru pagina solicitată.
Exemplul 1 – fără paginare
Mai întâi, să rulăm o interogare care returnează toate rândurile dintr-un tabel:
SELECT *FROM GenresORDER BY GenreId;
rezultat:
+-----------+---------+| GenreId | Genre ||-----------+---------|| 1 | Rock || 2 | Jazz || 3 | Country || 4 | Pop || 5 | Blues || 6 | Hip Hop || 7 | Rap || 8 | Punk |+-----------+---------+
acest exemplu nu folosește paginare – toate rezultatele sunt afișate.
Acest set de rezultate este atât de mic încât în mod normal nu ar necesita paginare, dar în sensul acestui articol, să-l paginăm.
Exemplul 2 – afișează primele 3 rezultate
acest exemplu afișează primele trei rezultate:
SELECT *FROM GenresORDER BY GenreId OFFSET 0 ROWS FETCH NEXT 3 ROWS ONLY;
rezultat:
+-----------+---------+| GenreId | Genre ||-----------+---------|| 1 | Rock || 2 | Jazz || 3 | Country |+-----------+---------+
în acest caz, precizez că rezultatele ar trebui să înceapă de la primul rezultat și să afișeze următoarele trei rânduri. Acest lucru se face folosind următoarele:
-
OFFSET 0 ROWSspecifică faptul că nu ar trebui să existe nicio compensare (o compensare de zero). -
FETCH NEXT 3 ROWS ONLYprimește următoarele trei rânduri din offset. Din moment ce am specificat un offset de zero, primele trei rânduri sunt preluate.
dacă tot ce ne-am dorit ar fi fost primele 3 rezultate, am fi putut obține același rezultat folosind clauzaTOP în loc să specificăm valorile offset și fetch. Cu toate acestea, acest lucru nu ne-ar fi permis să facem următoarea parte.
Exemplul 3 – afișează următoarele 3 rezultate
acum să afișăm următoarele trei rezultate:
SELECT *FROM GenresORDER BY GenreId OFFSET 3 ROWS FETCH NEXT 3 ROWS ONLY;
rezultat:
+-----------+---------+| GenreId | Genre ||-----------+---------|| 4 | Pop || 5 | Blues || 6 | Hip Hop |+-----------+---------+
deci singurul lucru pe care l-am schimbat a fost offset-ul.
Valorile de offset și de preluare pot fi, de asemenea, o expresie furnizată ca o subinterogare scalară variabilă, parametru sau constantă. Atunci când se utilizează o subinterogare, aceasta nu poate face referire la nicio coloană definită în domeniul interogării exterioare (nu poate fi corelată cu interogarea exterioară).
următoarele exemple folosesc expresii pentru a arăta două abordări pentru paginarea rezultatelor.
Exemplul 4 – paginarea după numărul rândului
acest exemplu folosește expresii pentru a specifica numărul rândului la care să înceapă.
DECLARE @StartRow int = 1, @RowsPerPage int = 3; SELECT * FROM GenresORDER BY GenreId ASC OFFSET @StartRow - 1 ROWS FETCH NEXT @RowsPerPage ROWS ONLY;
rezultat:
+-----------+---------+| GenreId | Genre ||-----------+---------|| 1 | Rock || 2 | Jazz || 3 | Country |+-----------+---------+
aici, folosesc@StartRow int = 1 pentru a specifica că rezultatele ar trebui să înceapă de la primul rând.
Iată ce se întâmplă dacă increment această valoare la2.
DECLARE @StartRow int = 2, @RowsPerPage int = 3; SELECT * FROM GenresORDER BY GenreId ASC OFFSET @StartRow - 1 ROWS FETCH NEXT @RowsPerPage ROWS ONLY;
rezultat:
+-----------+---------+| GenreId | Genre ||-----------+---------|| 2 | Jazz || 3 | Country || 4 | Pop |+-----------+---------+
începe de la al doilea rând. Folosind această metodă, pot specifica rândul exact la care să încep.
exemplul 5 – paginarea după numărul paginii
acest exemplu este aproape identic cu exemplul anterior, cu excepția faptului că vă permite să specificați numărul paginii, spre deosebire de numărul rândului.
DECLARE @PageNumber int = 1, @RowsPerPage int = 3; SELECT * FROM GenresORDER BY GenreId ASC OFFSET (@PageNumber - 1) * @RowsPerPage ROWS FETCH NEXT @RowsPerPage ROWS ONLY;
rezultat:
+-----------+---------+| GenreId | Genre ||-----------+---------|| 1 | Rock || 2 | Jazz || 3 | Country |+-----------+---------+
deci primul rezultat este același. Cu toate acestea, să vedem ce se întâmplă atunci când incrementăm @PageNumber la 2 (am redenumit această variabilă pentru a reflecta noul său scop).
DECLARE @PageNumber int = 2, @RowsPerPage int = 3; SELECT * FROM GenresORDER BY GenreId ASC OFFSET (@PageNumber - 1) * @RowsPerPage ROWS FETCH NEXT @RowsPerPage ROWS ONLY;
rezultat:
+-----------+---------+| GenreId | Genre ||-----------+---------|| 4 | Pop || 5 | Blues || 6 | Hip Hop |+-----------+---------+
de data aceasta rezultatele încep de la al patrulea rând. Deci, folosind această metodă, puteți trece pur și simplu numărul paginii, mai degrabă decât numărul rândului.
exemplu 6 – paginare buclă
pentru a termina, aici este un exemplu rapid care bucle prin toate paginile și specifică numărul rândului de pornire pentru fiecare iterație:
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;
rezultat:
+-----------+---------+| 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)
exemplu 7 – rând vs rânduri
Dacă întâlniți cod care utilizează ROW în loc de ROWS, ambele argumente fac același lucru. Acestea sunt sinonime și sunt prevăzute pentru compatibilitatea ANSI.
Iată primul exemplu de pe această pagină, dar cuROW în loc deROWS.
SELECT *FROM GenresORDER BY GenreId OFFSET 0 ROW FETCH NEXT 3 ROW ONLY;
rezultat:
+-----------+---------+| GenreId | Genre ||-----------+---------|| 1 | Rock || 2 | Jazz || 3 | Country |+-----------+---------+
exemplul 8 – primul vs următorul
același lucru se aplică FIRST și NEXT. Acestea sunt sinonime prevăzute pentru compatibilitatea ANSI.
Iată exemplul anterior, dar cuFIRST în loc deNEXT.
SELECT *FROM GenresORDER BY GenreId OFFSET 0 ROW FETCH FIRST 3 ROW ONLY;
rezultat:
+-----------+---------+| GenreId | Genre ||-----------+---------|| 1 | Rock || 2 | Jazz || 3 | Country |+-----------+---------+