Paginacja jest procesem, który jest używany do dzielenia dużych danych na mniejsze dyskretne strony, a proces ten jest również znany jako stronicowanie. Paginacja jest powszechnie używana przez aplikacje internetowe i można ją zaobserwować w Google. Kiedy szukamy czegoś w Google, pokazuje wyniki na oddzielnej stronie; to jest główna idea paginacji.
Teraz, omówimy jak osiągnąć paginację w SQL Server w następnych częściach artykułu.
Przygotowanie przykładowych danych
Zanim zaczniemy zagłębiać się w szczegóły paginacji, utworzymy przykładową tabelę i wypełnimy ją pewnymi syntetycznymi danymi. W poniższym zapytaniu utworzymy tabelę SampleFruits przechowującą nazwy owoców oraz ich ceny sprzedaży. W dalszej części artykułu będziemy korzystać z tej tabeli.
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
|
CREATE TABLE SampleFruits (
Id INT PRIMARY KEY IDENTITY(1,1) ,
FruitName VARCHAR(50) ,
Price INT
)
GO
INSERT INTO SampleFruits VALUES(’Apple’,20)
INSERT INTO SampleFruits VALUES(’Apricot’,12)
INSERT INTO SampleFruits VALUES(’Banana’,8)
INSERT INTO SampleFruits VALUES(’Cherry’,11)
INSERT INTO SampleFruits VALUES(’Strawberry’,26)
INSERT INTO SampleFruits VALUES(’Lemon’,4)
INSERT INTO SampleFruits VALUES(’Kiwi’,14)
INSERT INTO SampleFruits VALUES(’Coconut’,34)
INSERT INTO SampleFruits VALUES(’Orange’,24)
INSERT INTO SampleFruits VALUES(’Raspberry’,13)
INSERT INTO SampleFruits VALUES(’Mango’,9)
INSERT INTO SampleFruits VALUES(’Mandarynka’,19)
INSERT INTO SampleFruits VALUES(’Ananas’,22)
GO
SELECT * FROM SampleFruits
|
Co to jest paginacja w SQL Server?
W odniesieniu do SQL Server, celem paginacji jest, podzielenie zbioru wyników na dyskretne strony za pomocą zapytania. Kiedy argumenty OFFSET i FETCH zostaną użyte wraz z klauzulą ORDER BY w instrukcji SELECT, będzie to rozwiązanie paginacji dla SQL Server.
OFFSET argument określa, ile wierszy zostanie pominiętych w zbiorze wyników zapytania. W poniższym przykładzie zapytanie pominie pierwsze 3 wiersze tabeli SampleFruits, a następnie zwróci wszystkie pozostałe wiersze.
|
1
2
3
4
|
SELECT FruitName, Price
FROM SampleFruits
ORDER BY Price
OFFSET 3 ROWS
|
Gdy ustawimy wartość OFFSET jako 0, żadne wiersze nie zostaną pominięte w zbiorze wyników. Poniższe zapytanie może być przykładem tego typu użycia:
|
1
2
3
|
SELECT FruitName,Price FROM SampleFruits
ORDER BY Price
OFFSET 0 ROWS
|
Z drugiej strony, jeśli ustawimy wartość OFFSET, która jest większa niż całkowita liczba wierszy w zbiorze wyników, w wyniku nie zostaną wyświetlone żadne wiersze. Rozważając poniższe zapytanie, całkowita liczba wierszy tabeli SampleFruits wynosi 13, a my ustawiliśmy wartość OFFSET na 20, więc zapytanie nie wyświetli żadnego wyniku.
|
1
2
3
|
SELECT FruitName,Price FROM SampleFruits
ORDER BY Price
OFFSET 20 ROWS
|
Argument FETCH określa, ile wierszy zostanie wyświetlonych w wyniku, a argument FETCH musi być użyty wraz z argumentem OFFSET. W poniższym przykładzie pominiemy pierwsze 5 wierszy, a następnie ograniczymy zbiór wyników do 6 wierszy dla naszej przykładowej tabeli.
|
1
2
3
4
|
SELECT FruitName, Price
FROM SampleFruits
ORDER BY Price
OFFSET 5 ROWS FETCH NEXT 6 ROWS ONLY
|
Porada: Klauzula TOP CLAUSE ogranicza liczbę wierszy, które zostały zwrócone z instrukcji SELECT. Kiedy używamy klauzuli TOP bez ORDER BY, może ona zostać zwrócona do dowolnych wyników. Jeśli rozważymy poniższy przykład, zwróci on 3 losowe wiersze przy każdym wykonaniu zapytania.
|
1
2
|
SELECT TOP 7 FruitName, Price
FROM SampleFruits
|
Jak się dowiedzieliśmy, argument OFFSET-FETCH wymaga klauzuli ORDER BY w instrukcji SELECT. Jeśli chcemy zaimplementować niezdefiniowane zamówienie, które lubi poprzednie użycie klauzuli TOP z argumentami OFFSET-FETCH, możemy użyć zapytania, które wygląda jak poniżej:
|
1
2
3
|
SELECT FruitName ,Price FROM SampleFruits
ORDER BY (SELECT NULL)
OFFSET 0 ROWS FETCH NEXT 7 ROWS ONLY
|
.
Kwerenda paginacyjna w SQL Server
Po poznaniu odpowiedzi na pytanie „Co to jest paginacja?”, dowiemy się w jaki sposób możemy napisać zapytanie paginacyjne w SQL Server. Na początku wykonamy poniższe zapytanie i zajmiemy się jego wykonaniem:
|
1
2
3
4
5
6
7
8
|
DECLARE @PageNumber AS INT
DECLARE @RowsOfPage AS INT
SET @PageNumber=2
SET @RowsOfPage=4
SELECT FruitName,Price FROM SampleFruits
ORDER BY Price
OFFSET (@PageNumber-1)*@RowsOfPage ROWS
FETCH NEXT @RowsOfPage ROWS ONLY
|
Jak widzimy, w powyższym zapytaniu zadeklarowaliśmy dwie zmienne, a zmienne te to:
- @PageNumber – Określa ona numer strony, która zostanie wyświetlona
- @RowsOfPage – Określa ona ile numerów wierszy zostanie wyświetlonych na stronie. W rezultacie instrukcja SELECT wyświetli drugą stronę, która zawiera 4 wiersze
Sortowanie dynamiczne z paginacją
Aplikacje mogą wymagać sortowania danych według różnych kolumn w porządku rosnącym lub malejącym oprócz paginacji. Aby sprostać tego typu wymaganiom, możemy użyć klauzuli ORDER BY z warunkami CASE, dzięki czemu otrzymamy zapytanie, które może być posortowane według zmiennych. Poniższe zapytanie może być przykładem tego typu użycia:
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
|
DECLARE @PageNumber AS INT
DECLARE @RowsOfPage AS INT
DECLARE @SortingCol AS VARCHAR(100) =’FruitName’
. DECLARE @SortType AS VARCHAR(100) = 'DESC’
SET @PageNumber=1
SET @RowsOfPage=4
SELECT FruitName,Price FROM SampleFruits
ORDER BY
CASE WHEN @SortingCol = 'Price’ AND @SortType =’ASC’ THEN Price END ,
CASE WHEN @SortingCol = 'Price’ AND @SortType =’DESC’ THEN Price END DESC,
CASE WHEN @SortingCol = 'FruitName’ AND @SortType =’ASC’ THEN FruitName END ,
CASE WHEN @SortingCol = 'FruitName’ AND @SortType =’DESC’ THEN FruitName END DESC
OFFSET (@PageNumber-1)*@RowsOfPage ROWS
FETCH NEXT @RowsOfPage ROWS ONLY
|
Ponadto, możemy zmienić kolumnę sortowania i kierunek sortowania poprzez zmienne dla powyższego zapytania.
Paginacja w pętli
W tym przykładzie, nauczymy się techniki zapytań, która zwraca wszystkie wyniki stron dyskretnych za pomocą jednego zapytania.
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
|
DECLARE @PageNumber AS INT
DECLARE @RowsOfPage AS INT
DECLARE @MaxTablePage AS FLOAT
SET @PageNumber=1
SET @RowsOfPage=4
SELECT @MaxTablePage = COUNT(*) FROM SampleFruits
SET @MaxTablePage = CEILING(@MaxTablePage/@RowsOfPage)
WHILE @MaxTablePage >= @PageNumber
BEGIN
SELECT FruitName,Price FROM SampleFruits
ORDER BY Price
OFFSET (@PageNumber-1)*@RowsOfPage ROWS
FETCH NEXT @RowsOfPage ROWS ONLY
SET @PageNumber = @PageNumber + 1
END
|
Dla tego zapytania, stworzyliśmy dość prostą formułę. Na początku przypisaliśmy całkowitą liczbę wierszy tabeli SampleFruit do zmiennej @MaxTablePage, a następnie podzieliliśmy ją na to, ile wierszy będzie wyświetlanych na stronie. W ten sposób obliczyliśmy liczbę stron, które zostaną wyświetlone. Obliczona wartość może być jednak liczbą dziesiętną, dlatego użyliśmy funkcji CEILING, aby zaokrąglić ją do najmniejszej liczby całkowitej, która jest większa od obliczonej liczby. W drugim kroku zaimplementowaliśmy WHILE-LOOP i iterowaliśmy zmienną @PageNumber aż do ostatniej strony numeru.
Podsumowanie
W tym artykule staraliśmy się znaleźć odpowiedź na pytanie „What is Pagination?”, szczególnie dla SQL Server. Argumenty OFFSET-FETCH pomagają zaimplementować ile wierszy chcemy pominąć i ile wierszy chcemy wyświetlić w zbiorze wyników, gdy używamy ich z klauzulą ORDER BY w instrukcji SELECT. I wreszcie, dowiedzieliśmy się, jak możemy osiągnąć paginację w SQL Server za pomocą tych argumentów.
- Autor
- Recent Posts
Większa część jego kariery koncentrowała się na administracji i rozwoju baz danych SQL Server. Jego obecne zainteresowania to administracja bazami danych i Business Intelligence. Można go znaleźć na LinkedIn.
View all posts by Esat Erkec
- Mity optymalizacji zapytań – 23 marca, 2021
- Symptomy Parameter Sniffing w SQL Server – 17 marca 2021
- Używanie automatycznej korekty planu do strojenia zapytań – 4 marca 2021