A paginálás egy olyan folyamat, amely egy nagy adatot kisebb, különálló oldalakra oszt, és ezt a folyamatot lapozásnak is nevezik. A paginálást gyakran használják a webes alkalmazások, és a Google-on is láthatjuk. Amikor a Google-on keresünk valamit, a találatokat különálló oldalon jeleníti meg; ez a lapozás lényege.
A cikk következő részeiben most arról lesz szó, hogyan valósítható meg a lapozás az SQL Serverben.
Mintaadatok előkészítése
Mielőtt elkezdenénk részletesen foglalkozni a lapozással, létrehozunk egy mintatáblát, és feltöltjük néhány szintetikus adattal. A következő lekérdezésben létrehozunk egy SampleFruits táblát, amely a gyümölcsök nevét és eladási árát tárolja. A cikk következő részében ezt a táblát fogjuk használni.
|
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(‘Mandarin’,19)
INSERT INTO SampleFruits VALUES(‘Ananász’,22)
GO
SELECT * FROM SampleFruits
|
Mi a paginálás az SQL Serverben?
Az SQL Server szempontjából a paginálás célja, egy eredményhalmaz különálló oldalakra osztása a lekérdezés segítségével. Ha az OFFSET és a FETCH argumentumokat a SELECT utasítás ORDER BY záradékával együtt használjuk, akkor ez az SQL Server oldalszámozási megoldása lesz.
Az OFFSET argumentum megadja, hogy hány sor kerül kihagyásra a lekérdezés eredményhalmazából. A következő példában a lekérdezés kihagyja a SampleFruits tábla első 3 sorát, majd visszaadja az összes többi sort.
|
1
2
3
4
|
SELECT FruitName, Price
FROM SampleFruits
ORDER BY Price
OFFSET 3 ROWS
|
Ha az OFFSET értékét 0-ra állítjuk, akkor nem hagyunk ki sorokat az eredményhalmazból. A következő lekérdezés lehet egy példa erre a felhasználási típusra:
|
1
2
3
|
SELECT FruitName,Price FROM SampleFruits
ORDER BY Price
OFFSET 0 ROWS
|
Másrészt, ha olyan OFFSET értéket állítunk be, amely nagyobb, mint az eredményhalmaz összes sorszáma, akkor nem jelenik meg sor az eredményben. Ha a következő lekérdezést tekintjük, a SampleFruits tábla teljes sorszáma 13, és az OFFSET értéket 20-ra állítottuk be, így a lekérdezés nem fog eredményt megjeleníteni.
|
1
2
3
|
SELECT FruitName,Price FROM SampleFruits
ORDER BY Price
OFFSET 20 ROWS
|
A FETCH argumentum meghatározza, hogy hány sor jelenjen meg az eredményben, és a FETCH argumentumot az OFFSET argumentummal együtt kell használni. A következő példában az első 5 sort kihagyjuk, majd az eredményhalmazt 6 sorra korlátozzuk a mintatáblánk esetében.
|
1
2
3
4
|
SELECT FruitName, Price
FROM SampleFruits
ORDER BY Price
OFFSET 5 ROWS FETCH NEXT 6 ROWS ONLY
|
Tip: A TOP CLAUSE korlátozza a SELECT utasításból visszaadott sorok számát. Ha a TOP záradékot ORDER BY nélkül használjuk, akkor tetszőleges eredményt adhatunk vissza. Ha a következő példát tekintjük, akkor a lekérdezés minden egyes végrehajtásakor 3 véletlenszerű sort fog visszaadni.
|
1
2
|
SELECT TOP 7 FruitName, Price
FROM SampleFruits
|
Mint tanultuk, az OFFSET-FETCH argumentum megköveteli a SELECT utasítás ORDER BY záradékát. Ha definiálatlan sorrendet szeretnénk megvalósítani, amely tetszik a TOP záradék korábbi használata OFFSET-FETCH argumentummal, akkor egy olyan lekérdezést használhatunk, amely az alábbiak szerint néz ki:
|
1
2
3
|
SELECT FruitName ,Price FROM SampleFruits
ORDER BY (SELECT NULL)
OFFSET 0 ROWS FETCH NEXT 7 ROWS ONLY
|
Pagination query in SQL Server
Miután kitalálta a választ a “Mi az a Pagination?” kérdésre, megtanuljuk, hogyan írhatunk paginálási lekérdezést az SQL Serverben. Először a következő lekérdezést fogjuk végrehajtani, és megbirkózunk a lekérdezéssel:
|
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
|
Mint láthatjuk, a fenti lekérdezésben két változót deklaráltunk, és ezek a változók a következők:
- @PageNumber – Megadja a megjelenítendő oldal számát
- @RowsOfPage – Megadja, hogy hány sorszám jelenik meg az oldalon. Ennek eredményeképpen a SELECT utasítás a második oldalt jeleníti meg, amely 4 sort tartalmaz
Dinamikus rendezés lapozással
Az alkalmazásoknak szükségük lehet arra, hogy a lapozás mellett az adatokat különböző oszlopok szerint akár növekvő, akár csökkenő sorrendbe rendezzék. Az ilyen típusú követelmények leküzdésére használhatunk egy ORDER BY záradékot CASE feltételekkel, így olyan lekérdezést kapunk, amelyet a változók szerint rendezhetünk. A következő lekérdezés lehet egy példa erre a felhasználási típusra:
|
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
|
Also, a fenti lekérdezéshez a változókon keresztül megváltoztathatjuk a rendezési oszlopot és a rendezési irányt.
Pagination in a Loop
Ebben a példában egy olyan lekérdezési technikát ismerünk meg, amely egyetlen lekérdezéssel adja vissza az összes diszkrét oldal eredményét.
|
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
|
Ehhez a lekérdezéshez, egy elég egyszerű képletet hoztunk létre. Először a SampleFruit tábla összes sorszámát hozzárendeltük a @MaxTablePage változóhoz, majd elosztottuk, hogy hány sor jelenik meg egy oldalon. Így kiszámoltuk a megjelenítendő oldalak számát. A kiszámított érték azonban lehet tizedesjegy, és ehhez a CEILING függvényt használtuk, hogy felkerekítsük a legkisebb egész számra, amely nagyobb a kiszámított számnál. Második lépésként egy WHILE-LOOP-ot implementáltunk, és a @PageNumber változót az utolsó oldalszámig iteráltuk.
Következtetés
Ebben a cikkben megpróbáltuk kideríteni a választ a “Mi a Pagination?” kérdésre, különösen az SQL Server esetében. Az OFFSET-FETCH argumentumok segítenek megvalósítani, hogy hány sort szeretnénk kihagyni és hány sort szeretnénk megjeleníteni az eredményhalmazban, amikor a SELECT utasításokban az ORDER BY záradékkal együtt használjuk őket. Végül pedig megtudtuk, hogyan érhetjük el a lapozást az SQL Serverben ezekkel az argumentumokkal.
- Author
- Recent Posts
Karrierje nagy részében SQL Server adatbázis-adminisztrációval és fejlesztéssel foglalkozott. Jelenlegi érdeklődési területe az adatbázis-adminisztráció és az üzleti intelligencia. Megtalálható a LinkedIn-en.
Esat Erkec összes bejegyzése
- A lekérdezésoptimalizálás mítoszai – március 23, 2021
- A paraméterszipolyozás tünetei az SQL Serverben – 2021. március 17.
- Az automatikus tervkorrekció használata a lekérdezések hangolásához – 2021. március 4.