Paginering er en proces, der bruges til at opdele store data i mindre diskrete sider, og denne proces er også kendt som paginering. Paginering er almindeligt anvendt af webapplikationer og kan ses på Google. Når vi søger efter noget på Google, viser den resultaterne på den adskilte side; dette er hovedidéen bag paginering.
Nu vil vi diskutere, hvordan man opnår paginering i SQL Server i de næste dele af artiklen.
Forberedelse af eksempeldata
Før vi begynder at gå i detaljer med paginering, opretter vi en eksempeltabel og fylder den med nogle syntetiske data. I den følgende forespørgsel opretter vi en SampleFruits-tabel, som gemmer frugtnavne og salgspriser. I den næste del af artiklen vil vi bruge denne tabel.
|
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(‘Abrikos’,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(‘Kokosnød’,34)
INSERT INTO SampleFruits VALUES(‘Appelsin’,24)
INSERT INTO SampleFruits VALUES(‘Hindbær’,13)
INSERT INTO SampleFruits VALUES(‘Mango’,9)
INSERT INTO SampleFruits VALUES(‘Mandarin’,19)
INSERT INTO SampleFruits VALUES(‘Ananas’,22)
GO
SELECT * FROM SampleFruits
|
Hvad er pagination i SQL Server?
Med hensyn til SQL Server er formålet med paginering, at opdele et resultatsæt i diskrete sider ved hjælp af forespørgslen. Når OFFSET- og FETCH-argumenterne bruges sammen med ORDER BY-klausulen i en SELECT-angivelse, vil det være en pagineringsløsning for SQL Server.
OFFSET-argumentet angiver, hvor mange rækker der vil blive sprunget over fra forespørgslens resultatsæt. I det følgende eksempel vil forespørgslen springe de første 3 rækker i tabellen SampleFruits over og derefter returnere alle de resterende rækker.
|
1
2
3
4
|
SELECT FruitName, Price
FROM SampleFruits
ORDER BY Price
OFFSET 3 ROWS
|
Når vi sætter OFFSET-værdien til 0, vil ingen rækker blive sprunget over fra resultatsættet. Følgende forespørgsel kan være et eksempel på denne brugstype:
|
1
2
3
|
SELECT FruitName,Price FROM SampleFruits
ORDER BY Price
OFFSET 0 ROWS
|
På den anden side, hvis vi indstiller OFFSET-værdien, som er større end det samlede antal rækker i resultatsættet, vil ingen rækker blive vist på resultatet. Når vi ser på følgende forespørgsel, er det samlede antal rækker i tabellen SampleFruits 13, og vi indstiller OFFSET-værdien til 20, så forespørgslen vil ikke vise noget resultat.
|
1
2
3
|
SELECT FruitName,Pris FROM SampleFruits
ORDER BY Pris
OFFSET 20 RÆKKER
|
FETCH-argumentet angiver, hvor mange rækker der skal vises i resultatet, og FETCH-argumentet skal bruges sammen med OFFSET-argumentet. I det følgende eksempel vil vi springe de første 5 rækker over og derefter begrænse resultatsættet til 6 rækker for vores eksempeltabel.
|
1
2
3
4
|
SELECT FruitName, Price
FROM SampleFruits
ORDER BY Price
OFFSET 5 ROWS FETCH NEXT 6 ROWS ONLY
|
Tip: TOP CLAUSE begrænser antallet af rækker, der returneres fra SELECT-erklæringen. Når vi bruger TOP-klausulen uden ORDER BY, kan den returneres til vilkårlige resultater. Når vi betragter det følgende eksempel, vil det returnere 3 tilfældige rækker ved hver udførelse af forespørgslen.
|
1
2
|
SELECT TOP 7 FruitName, Pris
FROM SampleFruits
|
Som vi lærte, kræver OFFSET-FETCH-argumentet ORDER BY-klausulen i SELECT-erklæringen. Hvis vi ønsker at implementere en udefineret rækkefølge, der ligner den tidligere brug af TOP-klausulen med OFFSET-FETCH-argumenter, kan vi bruge en forespørgsel, der ser ud som nedenfor:
|
1
2
3
|
SELECT FruitName ,Pris FROM SampleFruits
ORDER BY (SELECT NULL)
OFFSET 0 ROWS FETCH NEXT 7 ROWS ONLY
|
Pagination forespørgsel i SQL Server
Efter at have fundet ud af svaret på “Hvad er pagination?” spørgsmålet, vil vi lære, hvordan vi kan skrive en paginering forespørgsel i SQL Server. I første omgang vil vi udføre følgende forespørgsel og vil tackle forespørgslen:
|
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 erklæret to variabler i ovenstående forespørgsel, og disse variabler er:
- @PageNumber – Det angiver nummeret på den side, der skal vises
- @RowsOfPage – Det angiver, hvor mange antal rækker, der skal vises på siden. Som følge heraf viser SELECT-anvisningen den anden side, som indeholder 4 rækker
Dynamisk sortering med paginering
Programmer kan have behov for at sortere dataene efter forskellige kolonner enten i stigende eller faldende rækkefølge ved siden af paginering. For at løse denne type krav kan vi bruge en ORDER BY-klausul med CASE-betingelser, så vi får en forespørgsel, der kan sorteres efter variablerne. Følgende forespørgsel kan være et eksempel på denne brugstype:
|
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
|
Også, kan vi ændre sorteringskolonnen og sorteringsretningen via variablerne for ovenstående forespørgsel.
Pagination i en sløjfe
I dette eksempel lærer vi en forespørgselsteknik, der returnerer alle diskrete sideresultater med en enkelt forespørgsel.
|
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
|
For denne forespørgsel, har vi lavet en ret simpel formel. Først tildelte vi det samlede antal rækker i tabellen SampleFruit til variablen @MaxTablePage, og derefter delte vi det op i, hvor mange rækker der vil blive vist på en side. Vi har altså beregnet antallet af sider, der vil blive vist. Den beregnede værdi kan dog være et decimaltal, og derfor brugte vi CEILING-funktionen til at afrunde det opad til det mindste hele tal, der er større end det beregnede tal. Som et andet trin implementerede vi en WHILE-LOOP og itererede @PageNumber-variablen indtil den sidste side af nummeret.
Konklusion
I denne artikel forsøgte vi at finde svaret på spørgsmålet “Hvad er Pagination?”, især for SQL Server. OFFSET-FETCH-argumenterne hjælper med at implementere, hvor mange rækker vi ønsker at springe over, og hvor mange rækker vi ønsker at vise i resultatsættet, når vi bruger dem sammen med ORDER BY-klausulen i SELECT-angivelserne. Og endelig lærte vi, hvordan vi kan opnå paginering i SQL Server med disse argumenter.
- Author
- Recent Posts
Det meste af hans karriere har været fokuseret på SQL Server-databaseadministration og -udvikling. Hans nuværende interesser ligger inden for databaseadministration og Business Intelligence. Du kan finde ham på LinkedIn.
Se alle indlæg af Esat Erkec
- Myter om forespørgselsoptimering – 23. marts, 2021
- Symptomer på parameter sniffing i SQL Server – 17. marts 2021
- Brug af automatisk plankorrektion til forespørgselsjustering – 4. marts 2021