SQL 使用此查询实现分页（跳过/获取）功能
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/13220743/
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
Implement paging (skip / take) functionality with this query
提问by Lars Holdgaard
I have been trying to understand a little bit about how to implement custom paging in SQL, for instance reading articles like this one.
我一直在尝试了解如何在 SQL 中实现自定义分页，例如阅读这样的文章。
I have the following query, which works perfectly. But I would like to implement paging with this one.
我有以下查询，它完美地工作。但我想用这个实现分页。
SELECT TOP x PostId FROM ( SELECT PostId, MAX (Datemade) as LastDate
from dbForumEntry
group by PostId ) SubQueryAlias
order by LastDate desc
What is it I want
我想要什么
I have forum posts, with related entries. I want to get the posts with the latest added entries, so I can select the recently debated posts.
我有论坛帖子，有相关条目。我想获取包含最新添加条目的帖子，以便我可以选择最近有争议的帖子。
Now, I want to be able to get the "top 10 to 20 recently active posts", instead of "top 10".
现在，我希望能够获得“最近活跃的前 10 到 20 个帖子”，而不是“前 10 个”。
What have I tried
我试过什么
I have tried to implement the ROW functions as the one in the article, but really with no luck.
我试图实现 ROW 函数作为文章中的函数，但真的没有运气。
Any ideas how to implement it?
任何想法如何实施它？
回答by Radim K?hler
In SQL Server 2012it is very very easy
在SQL Server 2012 中，这非常容易
SELECT col1, col2, ...
FROM ...
WHERE ...
ORDER BY -- this is a MUST there must be ORDER BY statement
-- the paging comes here
OFFSET 10 ROWS -- skip 10 rows
FETCH NEXT 10 ROWS ONLY; -- take 10 rows
If we want to skip ORDER BY we can use
如果我们想跳过 ORDER BY 我们可以使用
SELECT col1, col2, ...
...
ORDER BY CURRENT_TIMESTAMP
OFFSET 10 ROWS -- skip 10 rows
FETCH NEXT 10 ROWS ONLY; -- take 10 rows
(I'd rather mark that as a hack - but it's used, e.g. by NHibernate. To use a wisely picked up column as ORDER BY is preferred way)
（我宁愿将其标记为 hack - 但它已被使用，例如被 NHibernate 使用。使用明智地选择的列作为 ORDER BY 是首选方式）
to answer the question:
回答这个问题：
--SQL SERVER 2012
SELECT PostId FROM
( SELECT PostId, MAX (Datemade) as LastDate
from dbForumEntry
group by PostId
) SubQueryAlias
order by LastDate desc
OFFSET 10 ROWS -- skip 10 rows
FETCH NEXT 10 ROWS ONLY; -- take 10 rows
New key words offsetand fetch next(just following SQL standards) were introduced.
引入了新的关键字offset和fetch next（仅遵循 SQL 标准）。
But I guess, that you are not using SQL Server 2012, right? In previous version it is a bit (little bit) difficult. Here is comparison and examples for all SQL server versions: here
但我想，您没有使用SQL Server 2012，对吧？在以前的版本中，它有点（有点）困难。这是所有 SQL 服务器版本的比较和示例：here
So, this could work in SQL Server 2008:
所以，这可以在SQL Server 2008 中工作：
-- SQL SERVER 2008
DECLARE @Start INT
DECLARE @End INT
SELECT @Start = 10,@End = 20;
;WITH PostCTE AS
( SELECT PostId, MAX (Datemade) as LastDate
,ROW_NUMBER() OVER (ORDER BY PostId) AS RowNumber
from dbForumEntry
group by PostId
)
SELECT PostId, LastDate
FROM PostCTE
WHERE RowNumber > @Start AND RowNumber <= @End
ORDER BY PostId
回答by Felipe V. R.
In order to do this in SQL Server, you must order the query by a column, so you can specify the rows you want.
为了在 SQL Server 中执行此操作，您必须按列对查询进行排序，以便您可以指定所需的行。
Example:
例子：
select * from table order by [some_column]
offset 10 rows
FETCH NEXT 10 rows only
And you can't use the "TOP" keyword when doing this.
并且在执行此操作时不能使用“TOP”关键字。
You can learn more here: https://technet.microsoft.com/pt-br/library/gg699618%28v=sql.110%29.aspx
您可以在此处了解更多信息：https: //technet.microsoft.com/pt-br/library/gg699618%28v=sql.110%29.aspx
回答by Tadej
SQL 2008
SQL 2008
Radim K?hler's answer works, but here is a shorter version:
Radim K?hler 的回答有效，但这里有一个较短的版本：
select top 20 * from
(
select *,
ROW_NUMBER() OVER (ORDER BY columnid) AS ROW_NUM
from tablename
) x
where ROW_NUM>10
回答by Nicolas Vinícius Sroczynski
OFFSET 10 ROWS -- skip 10 rows
FETCH NEXT 10 ROWS ONLY; -- take 10 rows
use this in the end of your select syntax. =)
在选择语法的末尾使用它。=)
回答by amoljdv06
You can use nested query for pagination as follow: Paging from 4 Row to 8 Row where CustomerId is primary key
您可以使用嵌套查询进行分页，如下所示：Paging from 4 Row to 8 Row 其中 CustomerId 是主键
SELECT Top 5 * FROM Customers WHERE Country='Germany' AND CustomerId Not in (SELECT Top 3 CustomerID FROM Customers WHERE Country='Germany' order by city) order by city;
SELECT Top 5 * FROM Customers WHERE Country='Germany' AND CustomerId Not in (SELECT Top 3 CustomerID FROM Customers WHERE Country='Germany' order by city) order by city;
