如何在 SQL Server 2008 中进行分页
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2244322/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to do pagination in SQL Server 2008
提问by Omu
How do you do pagination in SQL Server 2008 ?
你如何在 SQL Server 2008 中进行分页？
采纳答案by Adriaan Stander
You can try something like
你可以尝试类似的东西
DECLARE @Table TABLE(
Val VARCHAR(50)
)
DECLARE @PageSize INT,
@Page INT
SELECT @PageSize = 10,
@Page = 2
;WITH PageNumbers AS(
SELECT Val,
ROW_NUMBER() OVER(ORDER BY Val) ID
FROM @Table
)
SELECT *
FROM PageNumbers
WHERE ID BETWEEN ((@Page - 1) * @PageSize + 1)
AND (@Page * @PageSize)
回答by AdaTheDev
You can use ROW_NUMBER():
您可以使用ROW_NUMBER()：
Returns the sequential number of a row within a partition of a result set, starting at 1 for the first row in each partition.
返回结果集分区内行的序列号，每个分区中的第一行从 1 开始。
Example:
例子：
WITH CTEResults AS
(
SELECT IDColumn, SomeField, DateField, ROW_NUMBER() OVER (ORDER BY DateField) AS RowNum
FROM MyTable
)
SELECT *
FROM CTEResults
WHERE RowNum BETWEEN 10 AND 20;
回答by MarceBozu
SQL Server 2012 provides pagination functionality (see http://www.codeproject.com/Articles/442503/New-features-for-database-developers-in-SQL-Server)
SQL Server 2012 提供分页功能（参见http://www.codeproject.com/Articles/442503/New-features-for-database-developers-in-SQL-Server）
In SQL2008 you can do it this way:
在 SQL2008 中，您可以这样做：
declare @rowsPerPage as bigint;
declare @pageNum as bigint;
set @rowsPerPage=25;
set @pageNum=10;
With SQLPaging As (
Select Top(@rowsPerPage * @pageNum) ROW_NUMBER() OVER (ORDER BY ID asc)
as resultNum, *
FROM Employee )
select * from SQLPaging with (nolock) where resultNum > ((@pageNum - 1) * @rowsPerPage)
Prooven! It works and scales consistently.
证明！它始终如一地工作和扩展。
回答by RehmanAfridi
1) CREATE DUMMY DATA
1) 创建虚拟数据
CREATE TABLE #employee (EMPID INT IDENTITY, NAME VARCHAR(20))
DECLARE @id INT = 1
WHILE @id < 200
BEGIN
INSERT INTO #employee ( NAME ) VALUES ('employee_' + CAST(@id AS VARCHAR) )
SET @id = @id + 1
END
2) NOW APPLY THE SOLUTION.
2) 现在应用解决方案。
This case assumes EMPID to be unique and sorted column.
这种情况假设 EMPID 是唯一且已排序的列。
Off-course, you will apply it a different column...
当然，你会将它应用到不同的列......
DECLARE @pageSize INT = 20
SELECT * FROM (
SELECT *, PageNumber = CEILING(CAST(EMPID AS FLOAT)/@pageSize)
FROM #employee
) MyQuery
WHERE MyQuery.PageNumber = 1
回答by Ardalan Shahgholi
These are my solution for paging the result of query in SQL server side. I have added the concept of filtering and order by with one column. It is very efficient when you are paging and filtering and ordering in your Gridview.
这些是我在 SQL 服务器端对查询结果进行分页的解决方案。我添加了过滤和排序的概念，一列。当您在 Gridview 中进行分页、过滤和排序时，它非常有效。
Before testing, you have to create one sample table and insert some row in this table : (In real world you have to change Where clause considering your table field and maybe you have some join and subquery in main part of select)
在测试之前，您必须创建一个示例表并在该表中插入一些行：（在现实世界中，您必须考虑到您的表字段更改 Where 子句，并且您可能在 select 的主要部分有一些连接和子查询）
Create Table VLT
(
ID int IDentity(1,1),
Name nvarchar(50),
Tel Varchar(20)
)
GO
Insert INTO VLT
VALUES
('NAME' + Convert(varchar(10),@@identity),'FAMIL' + Convert(varchar(10),@@identity))
GO 500000
In SQL server 2008, you can use the CTE concept. Because of that, I have written two type of query for SQL server 2008+
在 SQL Server 2008 中，您可以使用 CTE 概念。因此，我为 SQL Server 2008+ 编写了两种类型的查询
-- SQL Server 2008+
-- SQL Server 2008+
DECLARE @PageNumber Int = 1200
DECLARE @PageSize INT = 200
DECLARE @SortByField int = 1 --The field used for sort by
DECLARE @SortOrder nvarchar(255) = 'ASC' --ASC or DESC
DECLARE @FilterType nvarchar(255) = 'None' --The filter type, as defined on the client side (None/Contain/NotContain/Match/NotMatch/True/False/)
DECLARE @FilterValue nvarchar(255) = '' --The value the user gave for the filter
DECLARE @FilterColumn int = 1 --The column to wich the filter is applied, represents the column number like when we send the information.
SELECT
Data.ID,
Data.Name,
Data.Tel
FROM
(
SELECT
ROW_NUMBER()
OVER( ORDER BY
CASE WHEN @SortByField = 1 AND @SortOrder = 'ASC'
THEN VLT.ID END ASC,
CASE WHEN @SortByField = 1 AND @SortOrder = 'DESC'
THEN VLT.ID END DESC,
CASE WHEN @SortByField = 2 AND @SortOrder = 'ASC'
THEN VLT.Name END ASC,
CASE WHEN @SortByField = 2 AND @SortOrder = 'DESC'
THEN VLT.Name END ASC,
CASE WHEN @SortByField = 3 AND @SortOrder = 'ASC'
THEN VLT.Tel END ASC,
CASE WHEN @SortByField = 3 AND @SortOrder = 'DESC'
THEN VLT.Tel END ASC
) AS RowNum
,*
FROM VLT
WHERE
( -- We apply the filter logic here
CASE
WHEN @FilterType = 'None' THEN 1
-- Name column filter
WHEN @FilterType = 'Contain' AND @FilterColumn = 1
AND ( -- In this case, when the filter value is empty, we want to show everything.
VLT.ID LIKE '%' + @FilterValue + '%'
OR
@FilterValue = ''
) THEN 1
WHEN @FilterType = 'NotContain' AND @FilterColumn = 1
AND ( -- In this case, when the filter value is empty, we want to show everything.
VLT.ID NOT LIKE '%' + @FilterValue + '%'
OR
@FilterValue = ''
) THEN 1
WHEN @FilterType = 'Match' AND @FilterColumn = 1
AND VLT.ID = @FilterValue THEN 1
WHEN @FilterType = 'NotMatch' AND @FilterColumn = 1
AND VLT.ID <> @FilterValue THEN 1
-- Name column filter
WHEN @FilterType = 'Contain' AND @FilterColumn = 2
AND ( -- In this case, when the filter value is empty, we want to show everything.
VLT.Name LIKE '%' + @FilterValue + '%'
OR
@FilterValue = ''
) THEN 1
WHEN @FilterType = 'NotContain' AND @FilterColumn = 2
AND ( -- In this case, when the filter value is empty, we want to show everything.
VLT.Name NOT LIKE '%' + @FilterValue + '%'
OR
@FilterValue = ''
) THEN 1
WHEN @FilterType = 'Match' AND @FilterColumn = 2
AND VLT.Name = @FilterValue THEN 1
WHEN @FilterType = 'NotMatch' AND @FilterColumn = 2
AND VLT.Name <> @FilterValue THEN 1
-- Tel column filter
WHEN @FilterType = 'Contain' AND @FilterColumn = 3
AND ( -- In this case, when the filter value is empty, we want to show everything.
VLT.Tel LIKE '%' + @FilterValue + '%'
OR
@FilterValue = ''
) THEN 1
WHEN @FilterType = 'NotContain' AND @FilterColumn = 3
AND ( -- In this case, when the filter value is empty, we want to show everything.
VLT.Tel NOT LIKE '%' + @FilterValue + '%'
OR
@FilterValue = ''
) THEN 1
WHEN @FilterType = 'Match' AND @FilterColumn = 3
AND VLT.Tel = @FilterValue THEN 1
WHEN @FilterType = 'NotMatch' AND @FilterColumn = 3
AND VLT.Tel <> @FilterValue THEN 1
END
) = 1
) AS Data
WHERE Data.RowNum > @PageSize * (@PageNumber - 1)
AND Data.RowNum <= @PageSize * @PageNumber
ORDER BY Data.RowNum
GO
And second solution with CTE in SQL server 2008+
和 SQL Server 2008+ 中 CTE 的第二个解决方案
DECLARE @PageNumber Int = 1200
DECLARE @PageSize INT = 200
DECLARE @SortByField int = 1 --The field used for sort by
DECLARE @SortOrder nvarchar(255) = 'ASC' --ASC or DESC
DECLARE @FilterType nvarchar(255) = 'None' --The filter type, as defined on the client side (None/Contain/NotContain/Match/NotMatch/True/False/)
DECLARE @FilterValue nvarchar(255) = '' --The value the user gave for the filter
DECLARE @FilterColumn int = 1 --The column to wich the filter is applied, represents the column number like when we send the information.
;WITH
Data_CTE
AS
(
SELECT
ROW_NUMBER()
OVER( ORDER BY
CASE WHEN @SortByField = 1 AND @SortOrder = 'ASC'
THEN VLT.ID END ASC,
CASE WHEN @SortByField = 1 AND @SortOrder = 'DESC'
THEN VLT.ID END DESC,
CASE WHEN @SortByField = 2 AND @SortOrder = 'ASC'
THEN VLT.Name END ASC,
CASE WHEN @SortByField = 2 AND @SortOrder = 'DESC'
THEN VLT.Name END ASC,
CASE WHEN @SortByField = 3 AND @SortOrder = 'ASC'
THEN VLT.Tel END ASC,
CASE WHEN @SortByField = 3 AND @SortOrder = 'DESC'
THEN VLT.Tel END ASC
) AS RowNum
,*
FROM VLT
WHERE
( -- We apply the filter logic here
CASE
WHEN @FilterType = 'None' THEN 1
-- Name column filter
WHEN @FilterType = 'Contain' AND @FilterColumn = 1
AND ( -- In this case, when the filter value is empty, we want to show everything.
VLT.ID LIKE '%' + @FilterValue + '%'
OR
@FilterValue = ''
) THEN 1
WHEN @FilterType = 'NotContain' AND @FilterColumn = 1
AND ( -- In this case, when the filter value is empty, we want to show everything.
VLT.ID NOT LIKE '%' + @FilterValue + '%'
OR
@FilterValue = ''
) THEN 1
WHEN @FilterType = 'Match' AND @FilterColumn = 1
AND VLT.ID = @FilterValue THEN 1
WHEN @FilterType = 'NotMatch' AND @FilterColumn = 1
AND VLT.ID <> @FilterValue THEN 1
-- Name column filter
WHEN @FilterType = 'Contain' AND @FilterColumn = 2
AND ( -- In this case, when the filter value is empty, we want to show everything.
VLT.Name LIKE '%' + @FilterValue + '%'
OR
@FilterValue = ''
) THEN 1
WHEN @FilterType = 'NotContain' AND @FilterColumn = 2
AND ( -- In this case, when the filter value is empty, we want to show everything.
VLT.Name NOT LIKE '%' + @FilterValue + '%'
OR
@FilterValue = ''
) THEN 1
WHEN @FilterType = 'Match' AND @FilterColumn = 2
AND VLT.Name = @FilterValue THEN 1
WHEN @FilterType = 'NotMatch' AND @FilterColumn = 2
AND VLT.Name <> @FilterValue THEN 1
-- Tel column filter
WHEN @FilterType = 'Contain' AND @FilterColumn = 3
AND ( -- In this case, when the filter value is empty, we want to show everything.
VLT.Tel LIKE '%' + @FilterValue + '%'
OR
@FilterValue = ''
) THEN 1
WHEN @FilterType = 'NotContain' AND @FilterColumn = 3
AND ( -- In this case, when the filter value is empty, we want to show everything.
VLT.Tel NOT LIKE '%' + @FilterValue + '%'
OR
@FilterValue = ''
) THEN 1
WHEN @FilterType = 'Match' AND @FilterColumn = 3
AND VLT.Tel = @FilterValue THEN 1
WHEN @FilterType = 'NotMatch' AND @FilterColumn = 3
AND VLT.Tel <> @FilterValue THEN 1
END
) = 1
)
SELECT
Data.ID,
Data.Name,
Data.Tel
FROM Data_CTE AS Data
WHERE Data.RowNum > @PageSize * (@PageNumber - 1)
AND Data.RowNum <= @PageSize * @PageNumber
ORDER BY Data.RowNum
回答by Nicolas Riousset
Another solution which works from SQL 2005 at least, is to use TOP with SELECT subqueries and ORDER BY clauses.
另一个至少适用于 SQL 2005 的解决方案是将 TOP 与 SELECT 子查询和 ORDER BY 子句一起使用。
In brief, retrieving page 2 rows with 10 rows per page is the same as retrieving the last 10 rows of the first 20 rows. Which translates into retrieving the first 20 rows with ASC order, and then the first 10 rows with DESC order, before ordering again using ASC.
简而言之，检索每页 10 行的第 2 页行与检索前 20 行的最后 10 行相同。这转化为使用 ASC 顺序检索前 20 行，然后使用 DESC 顺序检索前 10 行，然后再次使用 ASC 进行排序。
Example : Retrieving page 2 rows with 3 rows per page
示例：检索页面 2 行，每页 3 行
create table test(id integer);
insert into test values(1),(2),(3),(4),(5),(6),(7),(8),(9),(10);
select *
from (
select top 2 *
from (
select top (4) *
from test
order by id asc) tmp1
order by id desc) tmp1
order by id asc
回答by user7488677
SELECT DISTINCT Id,ParticipantId,ActivityDate,IsApproved,
IsDeclined,IsDeleted,SubmissionDate, IsResubmitted,
[CategoryId] Id,[CategoryName] Name,
[ActivityId] [Id],[ActivityName] Name,Points,
[UserId] [Id],Email,
ROW_NUMBER() OVER(ORDER BY Id desc) AS RowNum from
(SELECT DISTINCT
Id,ParticipantId,
ActivityDate,IsApproved,
IsDeclined,IsDeleted,
SubmissionDate, IsResubmitted,
[CategoryId] [CategoryId],[CategoryName] [CategoryName],
[ActivityId] [ActivityId],[ActivityName] [ActivityName],Points,
[UserId] [UserId],Email,
ROW_NUMBER() OVER(ORDER BY Id desc) AS RowNum from
(SELECT DISTINCT ASN.Id,
ASN.ParticipantId,ASN.ActivityDate,
ASN.IsApproved,ASN.IsDeclined,
ASN.IsDeleted,ASN.SubmissionDate,
CASE WHEN (SELECT COUNT(*) FROM FDS_ActivitySubmission WHERE ParentId=ASN.Id)>0 THEN CONVERT(BIT, 1) ELSE CONVERT(BIT, 0) END IsResubmitted,
AC.Id [CategoryId], AC.Name [CategoryName],
A.Id [ActivityId],A.Name [ActivityName],A.Points,
U.Id[UserId],U.Email
FROM
FDS_ActivitySubmission ASN WITH (NOLOCK)
INNER JOIN
FDS_ActivityCategory AC WITH (NOLOCK)
ON
AC.Id=ASN.ActivityCategoryId
INNER JOIN
FDS_ApproverDetails FDSA
ON
FDSA.ParticipantID=ASN.ParticipantID
INNER JOIN
FDS_ActivityJobRole FAJ
ON
FAJ.RoleId=FDSA.JobRoleId
INNER JOIN
FDS_Activity A WITH (NOLOCK)
ON
A.Id=ASN.ActivityId
INNER JOIN
Users U WITH (NOLOCK)
ON
ASN.ParticipantId=FDSA.ParticipantID
WHERE
IsDeclined=@IsDeclined AND IsApproved=@IsApproved AND ASN.IsDeleted=0
AND
ISNULL(U.Id,0)=ISNULL(@ApproverId,0)
AND ISNULL(ASN.IsDeleted,0)<>1)P)t where t.RowNum between
(((@PageNumber - 1) * @PageSize) + 1) AND (@PageNumber * PageSize)
AND t.IsDeclined=@IsDeclined AND t.IsApproved=@IsApproved AND t.IsDeleted = 0
AND (ISNULL(t.Id,0)=ISNULL(@SubmissionId,0)or ISNULL(@SubmissionId,0)<=0)
