paginacja jest często używana w aplikacjach, w których użytkownik może kliknąć Poprzedni / Następny, aby przejść do stron, które składają się na wyniki, lub kliknąć numer strony, aby przejść bezpośrednio do określonej strony.
podczas uruchamiania zapytań w SQL Server można paginować wyniki za pomocąOFFSET IFETCH argumentówORDER BY klauzuli. Argumenty te zostały wprowadzone w SQL Server 2012, dlatego możesz użyć tej techniki, jeśli masz SQL Server 2012 lub wyższy.
w tym kontekście paginacja polega na tym, że dzielisz wyniki zapytania na mniejsze części, a każdy z nich kontynuuje tam, gdzie zakończył się poprzedni. Na przykład, jeśli zapytanie zwraca 1000 wierszy, można je paginować tak, aby były zwracane w grupach po 100. Aplikacja może przekazać numer strony i rozmiar strony do SQL Server, a SQL Server może użyć go do zwrócenia tylko danych dla żądanej strony.
przykład 1 – Brak stronicowania
najpierw uruchom zapytanie, które zwróci wszystkie wiersze w tabeli:
SELECT *FROM GenresORDER BY GenreId;
Result:
+-----------+---------+| GenreId | Genre ||-----------+---------|| 1 | Rock || 2 | Jazz || 3 | Country || 4 | Pop || 5 | Blues || 6 | Hip Hop || 7 | Rap || 8 | Punk |+-----------+---------+
Ten przykład nie używa paginacji – wyświetlane są wszystkie wyniki.
ten zbiór wyników jest tak mały, że normalnie nie wymagałby paginacji, ale na potrzeby tego artykułu, paginujmy go.
przykład 2 – Wyświetlanie pierwszych 3 wyników
Ten przykład wyświetla trzy pierwsze wyniki:
SELECT *FROM GenresORDER BY GenreId OFFSET 0 ROWS FETCH NEXT 3 ROWS ONLY;
wynik:
+-----------+---------+| GenreId | Genre ||-----------+---------|| 1 | Rock || 2 | Jazz || 3 | Country |+-----------+---------+
w tym przypadku określam, że wyniki powinny zaczynać się od pierwszego wyniku i wyświetlać następne trzy wiersze. Odbywa się to za pomocą następujących:
-
OFFSET 0 ROWSokreśla, że nie powinno być przesunięcia (przesunięcie o zero). -
FETCH NEXT 3 ROWS ONLYpobiera następne trzy wiersze z offsetu. Ponieważ podałem przesunięcie o zero, pobierane są pierwsze trzy wiersze.
gdybyśmy chcieli uzyskać 3 najlepsze wyniki, moglibyśmy osiągnąć ten sam wynik używając klauzuliTOP zamiast określać wartości offsetu i pobierania. Jednak to nie pozwoliłoby nam zrobić następnej części.
przykład 3 – Wyświetl następne 3 Wyniki
teraz wyświetlmy następne trzy wyniki:
SELECT *FROM GenresORDER BY GenreId OFFSET 3 ROWS FETCH NEXT 3 ROWS ONLY;
wynik:
+-----------+---------+| GenreId | Genre ||-----------+---------|| 4 | Pop || 5 | Blues || 6 | Hip Hop |+-----------+---------+
więc jedyne, co zmieniłem, to przesunięcie.
wartości offsetu i pobierania mogą być również wyrażeniem dostarczonym jako zmienna, parametr lub stałe podzbiory skalarne. Gdy używane jest zapytanie podrzędne, nie może odwoływać się do żadnych kolumn zdefiniowanych w zewnętrznym zakresie zapytania (nie może być skorelowane z zapytaniem zewnętrznym).
poniższe przykłady używają wyrażeń, aby pokazać dwa podejścia do stronicowania wyników.
przykład 4 – stronicowanie według numeru wiersza
Ten przykład używa wyrażeń do określenia numeru wiersza, od którego ma zacząć.
DECLARE @StartRow int = 1, @RowsPerPage int = 3; SELECT * FROM GenresORDER BY GenreId ASC OFFSET @StartRow - 1 ROWS FETCH NEXT @RowsPerPage ROWS ONLY;
wynik:
+-----------+---------+| GenreId | Genre ||-----------+---------|| 1 | Rock || 2 | Jazz || 3 | Country |+-----------+---------+
tutaj używam@StartRow int = 1, aby określić, że wyniki powinny zaczynać się od pierwszego wiersza.
oto, co się stanie, jeśli zwiększę tę wartość do 2.
DECLARE @StartRow int = 2, @RowsPerPage int = 3; SELECT * FROM GenresORDER BY GenreId ASC OFFSET @StartRow - 1 ROWS FETCH NEXT @RowsPerPage ROWS ONLY;
wynik:
+-----------+---------+| GenreId | Genre ||-----------+---------|| 2 | Jazz || 3 | Country || 4 | Pop |+-----------+---------+
zaczyna się w drugim wierszu. Korzystając z tej metody, mogę określić dokładny wiersz, od którego ma zacząć.
przykład 5 – stronicowanie według numeru strony
Ten przykład jest prawie identyczny z poprzednim, z tą różnicą, że pozwala na podanie numeru strony, w przeciwieństwie do numeru wiersza.
DECLARE @PageNumber int = 1, @RowsPerPage int = 3; SELECT * FROM GenresORDER BY GenreId ASC OFFSET (@PageNumber - 1) * @RowsPerPage ROWS FETCH NEXT @RowsPerPage ROWS ONLY;
wynik:
+-----------+---------+| GenreId | Genre ||-----------+---------|| 1 | Rock || 2 | Jazz || 3 | Country |+-----------+---------+
więc pierwszy wynik jest taki sam. Zobaczmy jednak, co się stanie, gdy zwiększymy@PageNumber do2 (zmieniłem nazwę tej zmiennej, aby odzwierciedlić jej nowy cel).
DECLARE @PageNumber int = 2, @RowsPerPage int = 3; SELECT * FROM GenresORDER BY GenreId ASC OFFSET (@PageNumber - 1) * @RowsPerPage ROWS FETCH NEXT @RowsPerPage ROWS ONLY;
wynik:
+-----------+---------+| GenreId | Genre ||-----------+---------|| 4 | Pop || 5 | Blues || 6 | Hip Hop |+-----------+---------+
tym razem wyniki zaczynają się od czwartego rzędu. Więc za pomocą tej metody można po prostu przekazać numer strony, a nie numer wiersza.
przykład 6 – pętla paginacji
aby zakończyć, oto szybki przykład, który pętli przez wszystkie strony i określa numer wiersza początkowego dla każdej iteracji:
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;
wynik:
+-----------+---------+| 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)
przykład 7 – wiersz vs wiersze
Jeśli napotkany kod, który używa ROW zamiast ROWS, oba argumenty robią to samo. Są to synonimy i są przewidziane dla zgodności z ANSI.
oto pierwszy przykład na tej stronie, ale zROW zamiastROWS.
SELECT *FROM GenresORDER BY GenreId OFFSET 0 ROW FETCH NEXT 3 ROW ONLY;
wynik:
+-----------+---------+| GenreId | Genre ||-----------+---------|| 1 | Rock || 2 | Jazz || 3 | Country |+-----------+---------+
przykład 8 – pierwszy vs następny
to samo dotyczy FIRST I NEXT. Są to synonimy przewidziane dla zgodności z ANSI.
oto poprzedni przykład, ale zFIRST zamiastNEXT.
SELECT *FROM GenresORDER BY GenreId OFFSET 0 ROW FETCH FIRST 3 ROW ONLY;
wynik:
+-----------+---------+| GenreId | Genre ||-----------+---------|| 1 | Rock || 2 | Jazz || 3 | Country |+-----------+---------+