通过此查询实现分页(跳过/获取)功能 [英] Implement paging (skip / take) functionality with this query
问题描述
我一直试图了解一些有关如何在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
(我宁愿将其标记为一种技巧-但它已被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 Server版本的比较和示例:此处
因此,这可以在 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
这篇关于通过此查询实现分页(跳过/获取)功能的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!