Paginering är en process som används för att dela upp stora data i mindre diskreta sidor, och den här processen kallas också för paging. Paginering används ofta av webbapplikationer och kan ses på Google. När vi söker efter något på Google visas resultaten på den separerade sidan, detta är huvudidén med paginering.
Nu kommer vi att diskutera hur man uppnår paginering i SQL Server i de kommande delarna av artikeln.
Förberedelse av provdata
Innan vi börjar gå in i detalj på paginering kommer vi att skapa en provtabell och fylla den med några syntetiska data. I följande fråga skapar vi en tabell SampleFruits som lagrar fruktnamn och försäljningspriser. I nästa del av artikeln kommer vi att använda denna tabell.
|
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(’Aprikos’,12)
INSERT INTO SampleFruits VALUES(’Banan’,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(’Ananas’,22)
GO
SELECT * FROM SampleFruits
|
Vad är Pagination i SQL Server?
När det gäller SQL Server är syftet med paginering att dela upp en resultatmängd i olika sidor med hjälp av en fråga. När OFFSET- och FETCH-argumenten används tillsammans med ORDER BY-klausulen i ett SELECT-meddelande blir det en pagineringslösning för SQL Server.
OFFSET-argumentet anger hur många rader som kommer att hoppas över från resultatmängden av frågan. I följande exempel kommer frågan att hoppa över de tre första raderna i tabellen SampleFruits och sedan returnera alla resterande rader.
|
1
2
3
4
|
SELECT FruitName, Price
FROM SampleFruits
ORDER BY Price
OFFSET 3 ROWS
|
När vi ställer in OFFSET-värdet på 0 kommer inga rader att hoppas över från resultatsamlingen. Följande fråga kan vara ett exempel på denna typ av användning:
|
1
2
3
|
SELECT FruitName,Price FROM SampleFruits
ORDER BY Price
OFFSET 0 ROWS
|
Om vi å andra sidan fastställer OFFSET-värdet, som är större än det totala antalet rader i resultatsamlingen, kommer inga rader att visas på resultatet. I följande fråga är det totala antalet rader i tabellen SampleFruits 13, och vi har satt OFFSET-värdet till 20, så frågan kommer inte att visa något resultat.
|
1
2
3
|
SELECT FruitName,Price FROM SampleFruits
ORDER BY Price
OFFSET 20 ROWS
|
FETCH-argumentet anger hur många rader som ska visas i resultatet och FETCH-argumentet måste användas med OFFSET-argumentet. I följande exempel hoppar vi över de första 5 raderna och begränsar sedan resultatmängden till 6 rader för vår exempeltabell.
|
1
2
3
4
|
SELECT FruitName, Price
FROM SampleFruits
ORDER BY Price
OFFSET 5 ROWS FETCH NEXT 6 ROWS ONLY
|
Tip: TOP CLAUSE begränsar antalet rader som returneras från SELECT-angivelsen. När vi använder TOP-klausulen utan ORDER BY kan det returneras till godtyckliga resultat. När vi betraktar följande exempel kommer det att återge 3 slumpmässiga rader vid varje körning av frågan.
|
1
2
|
SELECT TOP 7 FruitName, Price
FROM SampleFruits
|
Som vi lärde oss kräver OFFSET-FETCH-argumentet ORDER BY-klausulen i SELECT-angivelsen. Om vi vill implementera en odefinierad ordning som liknar den tidigare användningen av TOP-klausulen med OFFSET-FETCH-argument kan vi använda en fråga som ser ut som nedan:
|
1
2
3
|
SELECT FruitName ,Pris FROM SampleFruits
ORDER BY (SELECT NULL)
OFFSET 0 ROWS FETCH NEXT 7 ROWS ONLY
|
Pagineringsfråga i SQL Server
Efter att ha tagit reda på svaret på ”Vad är Pagination?” kommer vi att lära oss hur vi kan skriva en pagineringsfråga i SQL Server. Till att börja med kommer vi att exekvera följande fråga och kommer att ta itu med frågan:
|
1
2
3
4
5
6
7
8
|
DECLARE @PageNumber AS INT
DECLARE @RowsOfPage AS INT
SET @PageNumber=2
SET @RowsOfPage=4
SELECT FruitName,Pris FROM SampleFruits
ORDER BY Price
OFFSET (@PageNumber-1)*@RowsOfPage ROWS
FETCH NEXT @RowsOfPage ROWS ONLY
|
Som vi kan se, har vi deklarerat två variabler i ovanstående fråga, och dessa variabler är:
- @PageNumber – Anger numret på den sida som ska visas
- @RowsOfPage – Anger hur många rader som ska visas på sidan. Som ett resultat visar SELECT-anvisningen den andra sidan, som innehåller 4 rader
Dynamisk sortering med paginering
Applikationer kan behöva sortera data enligt olika kolumner antingen i stigande eller fallande ordning vid sidan av paginering. För att lösa den här typen av krav kan vi använda en ORDER BY-klausul med CASE-villkor så att vi får en fråga som kan sorteras efter variablerna. Följande fråga kan vara ett exempel på denna typ av användning:
|
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,Pris 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
|
Dessutom, kan vi ändra sorteringskolumnen och sorteringsriktningen med hjälp av variablerna för ovanstående fråga.
Paginering i en slinga
I det här exemplet lär vi oss en frågeteknik som returnerar alla diskreta sidresultat med en enda fråga.
|
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,Pris FROM SampleFruits
ORDER BY Price
OFFSET (@PageNumber-1)*@RowsOfPage ROWS
FETCH NEXT @RowsOfPage ROWS ONLY
SET @PageNumber = @PageNumber + 1
END
|
För denna fråga, skapade vi en ganska enkel formel. Först tilldelade vi det totala antalet rader i tabellen SampleFruit till variabeln @MaxTablePage och sedan delade vi upp det i hur många rader som ska visas på en sida. Vi har alltså beräknat antalet sidor som kommer att visas. Det beräknade värdet kan dock vara ett decimaltal, och för det använde vi CEILING-funktionen för att avrunda det uppåt till det minsta heltal som är större än det beräknade antalet. Som ett andra steg implementerade vi en WHILE-LOOP och itererade @PageNumber-variabeln tills den sista sidan i numret.
Slutsats
I den här artikeln försökte vi ta reda på svaret på frågan ”Vad är Pagination?”, särskilt för SQL Server. OFFSET-FETCH-argument hjälper till att implementera hur många rader vi vill hoppa över och hur många rader vi vill visa i resultatmängden när vi använder dem med ORDER BY-klausulen i SELECT-angivelserna. Och slutligen lärde vi oss hur vi kan uppnå paginering i SQL Server med dessa argument.
- Författare
- Recenta inlägg
Den största delen av hans karriär har varit inriktad på SQL Server-databasadministration och utveckling. För närvarande intresserar han sig för databasadministration och Business Intelligence. Du hittar honom på LinkedIn.
Se alla inlägg av Esat Erkec
- Myter om sökordsoptimering – 23 mars, 2021
- Symtom på parametersniffning i SQL Server – 17 mars 2021
- Användning av automatisk plankorrigering för att trimma frågor – 4 mars 2021