我一直在try 了解一点如何在SQL中实现自定义分页，例如reading articles like 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
我有论坛帖子和相关条目.我想获得最新添加条目的帖子，这样我就可以 Select 最近讨论的帖子.
现在，我希望能够获得"前10至20个最近活跃的帖子"，而不是"前10".
What have I tried
我曾try 实现本文中的ROW函数，但真的没有成功.
有什么 idea 可以实现吗？
在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
如果我们想跳过订单，我们可以使用
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)
要回答这个问题:
--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
引入了新的关键字offset和fetch next(仅遵循SQL标准).
But I guess, that you are not using 101, right? 在以前的版本中，这有点困难.下面是所有SQL server版本的比较和示例:here
所以，这可能在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