SQL Server 分页查询
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/15421761/
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
SQL Server paging query
提问by Raccoon
Urggggg! I've been struggling with this for a long time! I can do it with MySQL so easy but not with SQL Server :(
啊啊啊啊啊啊！我已经为此苦苦挣扎了很长时间！我可以很容易地用 MySQL 做到这一点，但不能用 SQL Server :(
Here are the simplified tables which should be joined all together.
这是应该连接在一起的简化表。
Combining all of them by using inner join syntax, I have to write a query to use for paging in the future (btw, PHP). Let's say I need all ID, Name, and Date info which a picture is taken between 2012-10-01 and 2012-10-30.... and 20 people per page.
通过使用内部连接语法组合所有这些，我必须编写一个查询以用于将来的分页（顺便说一句，PHP）。假设我需要在 2012 年 10 月 1 日和 2012 年 10 月 30 日之间拍摄的所有 ID、姓名和日期信息......以及每页 20 人。
What would be the easiest query to achieve the goal here? (I tried NOT IN.. but it was so buggy cuz I'm not used to 'NOT IN' stuff...)
在这里实现目标的最简单查询是什么？（我试过 NOT IN .. 但它太有问题了，因为我不习惯 'NOT IN' 的东西......）
You can ignore the performance speed.
您可以忽略性能速度。
Thank you!
谢谢！
回答by Mike Monteiro
This is how I would do it in SQL Server 2005+:
这就是我在 SQL Server 2005+ 中的做法：
SELECT ID, Name, Photo, CreatedDate, rowNum, (rowNum / 20) + 1 as pageNum
FROM (
SELECT a.ID, a.Name, b.Photo, c.Created_Date
, Row_Number() OVER (ORDER BY c.Created_Date ASC) as rowNum
FROM a
JOIN b ON a.ID = b.ID
JOIN c ON c.photo = b.photo
WHERE c.Created_Date BETWEEN '2012-01-1' AND '2012-10-30'
) x
WHERE (rowNum / 20) + 1 = 1
Note that I'm using a little integer division trickery to calculate page number.
请注意，我使用了一些整数除法技巧来计算页码。
Since pre-2005 sadly doesn't have row_number(), I'd use an intermediate table with an identity column:
由于遗憾的是 2005 年之前没有 row_number()，我将使用带有标识列的中间表：
SELECT a.ID, a.Name, b.Photo, c.Created_Date
, identity(int,1,1) as rowNum
INTO t
FROM a
JOIN b ON a.ID = b.ID
JOIN c ON c.photo = b.photo
WHERE c.Created_Date BETWEEN '2012-01-1' AND '2012-10-30'
ORDER BY c.Created_Date ASC
GO
ALTER TABLE t ADD pageNum AS rowNum / 20
GO
SELECT ID, Name, Photo, Created_Date, rowNum
FROM t
WHERE pageNum = 1
回答by Bill Rawlinson
Here is how I did it a long time ago..
这是我很久以前做的方法..
SELECT * FROM (
SELECT TOP y * FROM (
SELECT TOP x * FROM sometable
ORDER BY somefield ASC
)
ORDER BY somefield DESC)
ORDER BY somefield
The innermost query, SELECT TOP x, grabs the first x number of rows, the second query SELECT TOP y, gets the last y of x rows, and the outermost query, SELECT * puts the results in the right order.
最里面的查询 SELECT TOP x 获取前 x 行，第二个查询 SELECT TOP y 获取 x 行的最后 y，最外面的查询 SELECT * 将结果按正确的顺序排列。
Here is a blog post that explains how it works Here is a blog article I wrote back in 2006 talking about it http://code.rawlinson.us/2006/12/t-sql-query-paging.html
这是一篇解释它如何工作的博客文章 这是我在 2006 年写的一篇博客文章，讨论它http://code.rawlinson.us/2006/12/t-sql-query-paging.html
The tl;dr; of the post is from this paragraph:
tl;博士; 这篇文章来自这一段：
For example, let's say we want the first page, so the top 20 results. That is pretty easy, just use SELECT TOP 20 …. but what about the second or subsequent pages? How do you get the 21-40 items? It's easier than you might suspect. What you are actually trying to get is the bottom y of the top x results. To look at that another way you want the top y of the top x results ordered backwards.
例如，假设我们想要第一页，所以前 20 个结果。这很简单，只需使用 SELECT TOP 20 .... 但是第二页或后续页呢？你如何获得21-40个项目？这比您想象的要容易。您实际上想要得到的是顶部 x 结果的底部 y。从另一种角度来看，您希望前 x 结果的前 y 向后排序。
It's up to you how you calculate and provide the x and y values to the query.
如何计算并向查询提供 x 和 y 值取决于您。
回答by Peyman Mehrabani
Microsoft added native paging features in SQL Server 2012 and above using "OFFSET" and "FETCH". You can use this feature as below:
Microsoft 在 SQL Server 2012 及更高版本中使用"OFFSET" 和 "FETCH"添加了本机分页功能。您可以按如下方式使用此功能：
-- Skip the first 500 rows and return the next 100
SELECT *
FROM TableName
ORDER BY [ID]
OFFSET 500 ROWS
FETCH NEXT 100 ROWS ONLY;
For the OFFSET __and FETCH NEXT __clauses, you can specify constant values (as above), or you can specify variables, expressions, or constant scalar subqueries.
对于OFFSET __andFETCH NEXT __子句，您可以指定常量值（如上所述），也可以指定变量、表达式或常量标量子查询。
回答by wwanich
Try this with Sql Server 2008 + AdventureWorksdatabase
用 Sql Server 2008 + AdventureWorks数据库试试这个
DECLARE @PageIndex INT, @RowsPerPage INT
DECLARE @StartRow INT, @EndRow INT;
SET @PageIndex = 1;
SET @RowsPerPage = 500;
SET @StartRow = ((@PageIndex - 1) * @RowsPerPage) + 1;
SET @EndRow = @StartRow + @RowsPerPage - 1;
--append#1
WITH PAGE_ROWS
AS
(
SELECT ROW_NUMBER() OVER(ORDER BY OrderDate DESC, SalesOrderNumber ASC) AS ROW_NO
, COUNT(*) OVER() AS TOTAL_ROWS
, *
FROM(
--working query
SELECT S.SalesOrderID
, S.SalesOrderNumber
, S.OrderDate
, S.DueDate
, S.ShipDate
, S.Status
, S.PurchaseOrderNumber
, C.AccountNumber
, P.FirstName, P.MiddleName, P.LastName
FROM [Sales].[SalesOrderHeader] AS S
LEFT OUTER JOIN [Sales].[Customer] AS C ON C.CustomerID=S.CustomerID
LEFT OUTER JOIN [Person].[Person] AS P ON P.BusinessEntityID=C.PersonID
--append#2
) AS Src)
SELECT CEILING(TOTAL_ROWS/ CAST(@RowsPerPage AS DECIMAL(20,2))) AS TOTAL_PAGES
,*
FROM PAGE_ROWS
WHERE ROW_NO BETWEEN @StartRow AND @EndRow
ORDER BY OrderDate DESC, SalesOrderNumber ASC
回答by howard
I liked Taha Siddiqui's answer except that it requires modifying the passed in query and it didn't work for an SQL UNION statement that I have to use due to some very poor design decisions made by a former co-worker.
我喜欢 Taha Siddiqui 的回答，只是它需要修改传入的查询，并且它不适用于我必须使用的 SQL UNION 语句，因为我的前同事做出了一些非常糟糕的设计决策。
The generic SQL Server query is:
通用 SQL Server 查询是：
SELECT * FROM (
select ROW_NUMBER() OVER (order by ID) as row_num, * FROM (
<<Put Your Query Here>>
) AS tempTable1
) AS tempTable2 WHERE row_num >= ((pageNum -1) * pageSize) AND row_num < ((pageNum -1) * pageSize) + pageSize;
I created a Java function that assumes one-based paging:
我创建了一个 Java 函数，它假定基于一个的分页：
public static String buildPagingQuery(String sqlStr, String sortExpression, int pageNum, int pageSize) {
if (StringUtils.isBlank(sortExpression)) { //NOTE: uses org.apache.commons.lang.StringUtils
sortExpression = " (select 0)";
}
int startIndex = ((pageNum - 1) * pageSize) + 1;
int endIndex = startIndex + pageSize;
StringBuilder sb = new StringBuilder();
sb.append("SELECT * FROM (");
sb.append("SELECT ROW_NUMBER() OVER (ORDER BY ");
sb.append(sortExpression);
sb.append(") as row_num, * FROM (");
sb.append(sqlStr);
sb.append(") as tempTable1 ");
sb.append(") AS tempTable2 ");
sb.append("WHERE row_num >= ").append(startIndex);
sb.append(" AND row_num < ").append(endIndex);
return sb.toString();
}
I have not yet checked performance on a large table.
我还没有在大桌子上检查性能。
回答by Muhammad ADIL
ALTER Proc [dbo].[Sp_PagingonTable]
@SearchText varchar(255) = null,
@ChannelName varchar(255)= null,
@ChannelCategory varchar(255)= null,
@ChannelType varchar(255)= null,
@PageNo int,
@PageSize int,
@TotalPages int output
As
DECLARE @VariableTable TABLE
(
Rownumber INT,
ReferralHierarchyKey BIGINT,
ReferralID VARCHAR(255),
ChannelDetail VARCHAR(255),
ChannelName VARCHAR(255),
ChannelCategory VARCHAR(255),
ChannelType VARCHAR(255)
)
/*---Inserting Data into variable Table-------------*/
INSERT INTO @VariableTable
SELECT
ROW_NUMBER() OVER(ORDER BY [ColumnID) as Rownumber,*
FROM [dbo].[TableName]
WHERE (@SearchText IS NULL OR ChannelDetail LIKE '%' + @SearchText + '%') And (@ChannelName IS NULL OR ChannelName = @ChannelName )
/*--Applying Paging on filter Table--*/
SELECT
ReferralID
,ChannelDetail
,ChannelName
,ChannelCategory
,ChannelType
FROM
@VariableTable
WHERE Rownumber between (((@PageNo -1) *@PageSize)+1) and @PageNo * @PageSize
/*--Getting Total Pages After filter Table---*/
SELECT @TotalPages = (Count(*) + @PageSize - 1)/@PageSize FROM @VariableTable
SELECT @TotalPages
回答by Taha Rehman Siddiqui
You can use the methods from the following class
您可以使用以下类中的方法
internal class PagingHelper
{
public static String ParseQueryForPagingAndSorting(String strSQL, string SortExpression, int StartIndex, int EndIndex)
{
if (String.IsNullOrEmpty(SortExpression))
SortExpression = " (select 0)";
StringBuilder sb = new StringBuilder();
sb.Append("select * from (");
sb.Append(" select ROW_NUMBER() OVER (ORDER BY " + SortExpression + ") AS row_num,");
int index = strSQL.ToLower().IndexOf('t', 0);
sb.Append(strSQL.Substring(index + 2));
sb.Append(")");
sb.Append(" AS TempTable");
sb.Append(" where row_num>=" + StartIndex.ToString() + " AND row_num<=" + EndIndex.ToString());
return sb.ToString();
}
public static String ParseQueryForCount(String strSQL)
{
StringBuilder sb = new StringBuilder();
sb.Append("select count(*) from");
sb.Append("(");
sb.Append(strSQL);
sb.Append(")");
sb.Append(" AS TempTable");
return sb.ToString();
}
}
回答by Systematix Infotech
There is a another way I have found to do this into Sql server 2012
我发现还有另一种方法可以在 Sql server 2012 中执行此操作
http://raresql.com/2012/07/01/sql-paging-in-sql-server-2012-using-order-by-offset-and-fetch-next/
http://raresql.com/2012/07/01/sql-paging-in-sql-server-2012-using-order-by-offset-and-fetch-next/
