sql server分页

Pagination is a process that is used to divide a large data into smaller discrete pages, and this process is also known as paging. Pagination is commonly used by web applications and can be seen on Google. When we search for something on Google, it shows the results on the separated page; this is the main idea of the pagination.

分页是用于将大数据分为较小的离散页面的过程，该过程也称为分页。 分页是Web应用程序常用的，可以在Google上看到。 当我们在Google上搜索内容时，它会在单独的页面上显示结果； 这是分页的主要思想。

Now, we will discuss how to achieve pagination in SQL Server in the next parts of the article.

现在，在本文的下一部分中，我们将讨论如何在SQL Server中实现分页。

准备样品数据 (Preparing Sample Data)

Before beginning to go into detail about the pagination, we will create a sample table and will populate it with some synthetic data. In the following query, we will create a SampleFruits table that stores fruit names and selling prices. In the next part of the article, we will use this table.

在开始详细介绍分页之前，我们将创建一个示例表并使用一些综合数据填充它。 在以下查询中，我们将创建一个SampleFruits表，该表存储水果名称和售价。 在本文的下一部分中，我们将使用此表。

CREATE TABLE SampleFruits ( Id INT PRIMARY KEY IDENTITY(1,1) , FruitName VARCHAR(50) , Price INT)GOINSERT 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('Pineapple',22)GOSELECT * FROM SampleFruits

什么是SQL Server中的分页？ (What is Pagination in SQL Server?)

In terms of the SQL Server, the aim of the pagination is, dividing a resultset into discrete pages with the help of the query. When the OFFSET and FETCH arguments are used in with the ORDER BY clause in a SELECT statement, it will be a pagination solution for SQL Server.

就SQL Server而言，分页的目的是借助查询将结果集分为离散页面。 当将OFFSET和FETCH参数与SELECT语句中的ORDER BY子句一起使用时，它将是SQL Server的分页解决方案。

OFFSET argument specifies how many rows will be skipped from the resultset of the query. In the following example, the query will skip the first 3 rows of the SampleFruits table and then return all remaining rows.

OFFSET参数指定将从查询的结果集中跳过多少行。 在下面的示例中，查询将跳过SampleFruits表的前3行，然后返回所有剩余的行。

SELECT FruitName, PriceFROM SampleFruitsORDER BY PriceOFFSET 3 ROWS

When we set OFFSET value as 0, no rows will be skipped from the resultset. The following query can be an example of this usage type:

当我们将OFFSET值设置为0时，将不会从结果集中跳过任何行。 以下查询可以是这种用法类型的示例：

SELECT FruitName,Price FROM SampleFruitsORDER BY Price OFFSET 0 ROWS

On the other hand, if we set the OFFSET value, which is greater than the total row number of the resultset, no rows will be displayed on the result. When we consider the following query, the SampleFruits table total number of the rows is 13, and we set OFFSET value as 20, so the query will not display any result.

另一方面，如果我们设置OFFSET值，该值大于结果集的总行数，那么结果上将不显示任何行。 当我们考虑以下查询时， SampleFruits表的行总数为13，并且我们将OFFSET值设置为20，因此该查询将不显示任何结果。

SELECT FruitName,Price FROM SampleFruitsORDER BY Price OFFSET 20 ROWS

FETCH argument specifies how many rows will be displayed in the result, and the FETCH argument must be used with the OFFSET argument. In the following example, we will skip the first 5 rows and then limit the resultset to 6 rows for our sample table.

FETCH参数指定结果中将显示多少行，并且FETCH参数必须与OFFSET参数一起使用。 在下面的示例中，我们将跳过前5行，然后将示例表的结果集限制为6行。

SELECT FruitName, PriceFROM SampleFruitsORDER BY PriceOFFSET 5 ROWS FETCH NEXT 6 ROWS ONLY

Tip: The limits the number of rows that returned from the SELECT statement. When we use the TOP clause without ORDER BY, it can be returned to arbitrary results. When we consider the following example, it will return 3 random rows on each execution of the query.

提示： 限制从SELECT语句返回的行数。 当我们使用不带ORDER BY的TOP子句时，它可以返回到任意结果。 当我们考虑以下示例时，它将在每次执行查询时返回3个随机行。

SELECT TOP 7 FruitName, PriceFROM SampleFruits

As we learned, the OFFSET-FETCH argument requires the ORDER BY clause in the SELECT statement. If we want to implement an undefined order which likes the previous usage of the TOP clause with OFFSET-FETCH arguments, we can use a query which looks like below:

我们了解到， OFFSET-FETCH参数需要SELECT语句中的ORDER BY子句。 如果我们想实现一个未定义的顺序，就像以前使用带有OFFSET-FETCH参数的TOP子句一样，我们可以使用如下查询：

SELECT  FruitName ,Price FROM SampleFruitsORDER BY (SELECT NULL)OFFSET 0 ROWS FETCH NEXT 7 ROWS ONLY

SQL Server中的分页查询 (Pagination query in SQL Server)

After figuring out the answer to “What is Pagination?” question, we will learn how we can write a pagination query in SQL Server. At first, we will execute the following query and will tackle the query:

在弄清楚“什么是分页？”的答案之后 问题，我们将学习如何在SQL Server中编写分页查询。 首先，我们将执行以下查询并解决该查询：

DECLARE @PageNumber AS INTDECLARE @RowsOfPage AS INTSET @PageNumber=2SET @RowsOfPage=4SELECT FruitName,Price FROM SampleFruitsORDER BY Price OFFSET (@PageNumber-1)*@RowsOfPage ROWSFETCH NEXT @RowsOfPage ROWS ONLY

As we can see, we have declared two variables in the above query, and these variables are:

如我们所见，我们在上面的查询中声明了两个变量，这些变量是：

1. @PageNumber – It specifies the number of the page which will be displayed @PageNumber –指定要显示的页面号
2. @RowsOfPage – It specifies how many numbers of rows will be displayed on the page. As a result, the @RowsOfPage –它指定页面上将显示多少行。 结果， SELECT statement displays the second page, which contains 4 rows SELECT语句显示第二页，其中包含4行

分页动态排序 (Dynamic Sorting with Pagination)

Applications may need to sort the data according to different columns either in ascending or descending order beside pagination. To overcome this type of requirement, we can use an ORDER BY clause with CASE conditions so that we obtain a query that can be sorted by the variables. The following query can be an example of this usage type:

应用程序可能需要在分页之外按升序或降序按照不同的列对数据进行排序。 为了克服这种类型的要求，我们可以使用带有CASE条件的ORDER BY子句，以便获得可以按变量排序的查询。 以下查询可以是这种用法类型的示例：

DECLARE @PageNumber AS INTDECLARE @RowsOfPage AS INTDECLARE @SortingCol AS VARCHAR(100) ='FruitName'DECLARE @SortType AS VARCHAR(100) = 'DESC'SET @PageNumber=1SET @RowsOfPage=4SELECT FruitName,Price FROM SampleFruitsORDER 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 DESCOFFSET (@PageNumber-1)*@RowsOfPage ROWSFETCH NEXT @RowsOfPage ROWS ONLY

Also, we can change the sort column and sorting direction through the variables for the above query.

另外，我们可以通过上述查询的变量来更改排序列和排序方向。

分页循环 (Pagination in a Loop)

In this example, we will learn a query technique that returns all discrete page results with a single query.

在此示例中，我们将学习一种查询技术，该技术可通过单个查询返回所有离散页面结果。

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

For this query, we created a pretty simple formula. At first, we assigned the total row number of the SampleFruit table to the @MaxTablePage variable, and then we divided it into how many rows will be displayed on a page. So, we have calculated the number of pages that will be displayed. However, the calculated value can be a decimal, and for that, we used the CEILING function to round it up to the smallest integer number that is bigger than the calculated number. As a second step, we implemented a WHILE-LOOP and iterated @PageNumber variable until the last page of the number.

对于此查询，我们创建了一个非常简单的公式。 首先，我们将SampleFruit表的总行号分配给@MaxTablePage变量，然后将其划分为页面上将显示多少行。 因此，我们计算了将显示的页面数。 但是，计算出的值可以是十进制，为此，我们使用CEILING函数将其舍入为大于计算出的数字的最小整数。 第二步，我们实现了WHILE-LOOP并迭代@PageNumber变量直到数字的最后一页。

结论 (Conclusion)

In this article, we tried to find out the answer to “What is Pagination?” question, particularly for SQL Server. OFFSET-FETCH arguments help to implement how many rows we want to skip and how many rows we want to display in the resultset when we use them with the ORDER BY clause in the SELECT statements. And finally, we learned how we can achieve pagination in SQL Server with these arguments.

在本文中，我们试图找出“什么是分页？”的答案。 问题，尤其是对于SQL Server。 当我们将它们与SELECT语句中的ORDER BY子句一起使用时， OFFSET-FETCH参数有助于实现我们要跳过的行以及要在结果集中显示的行的数量。 最后，我们学习了如何使用这些参数在SQL Server中实现分页。

翻译自:

sql server分页