使用此查询实现分页(跳过/获取)功能

2021-12-02 00:00:00 join sql sql-server pagination

我一直试图了解如何在 SQL 中实现自定义分页，例如阅读 像这样的文章.

I have been trying to understand a little bit about how to implement custom paging in SQL, for instance reading articles like this one.

我有以下查询，效果很好.但是我想用这个实现分页.

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

我想要什么

我有论坛帖子和相关条目.我想获取包含最新添加条目的帖子，以便我可以选择最近讨论的帖子.

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.

现在，我希望能够获得最近活跃的前 10 到 20 个帖子"，而不是前 10 个".

Now, I want to be able to get the "top 10 to 20 recently active posts", instead of "top 10".

我尝试了什么

我曾尝试将 ROW 函数实现为文章中的函数，但确实没有运气.

I have tried to implement the ROW functions as the one in the article, but really with no luck.

任何想法如何实施它?

推荐答案

在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

如果我们想跳过 ORDER BY 我们可以使用

If we want to skip ORDER BY we can use

SELECT col1, col2, ...
  ...
 ORDER BY CURRENT_TIMESTAMP
OFFSET     10 ROWS       -- skip 10 rows
FETCH NEXT 10 ROWS ONLY; -- take 10 rows

(我宁愿将其标记为 hack - 但它已被使用，例如 NHibernate.使用明智地选择的列作为 ORDER BY 是首选方式)

回答问题:

--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标准).

New key words offset and fetch next (just following SQL standards) were introduced.

但我想，您没有使用 SQL Server 2012，对吗?在以前的版本中，它有点(有点)困难.以下是所有 SQL 服务器版本的比较和示例:这里

所以，这可以在 SQL Server 2008 中工作:

So, this could work in 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

相关文章