如何在SQL中选择“上一个”和“下一个”记录？

Question

我在我的网站上正在构建一个博客文章详细页面，该页面将显示类似于typepad博客文章的“上一篇”和“下一篇”链接。请参见以下示例。 我想知道是否有一种查询数据库的方法可以实现像下面这张图片一样的功能，它选择了“当前”记录（黄色），但也选择了按PublishDate排序时的前后记录（绿色）。 另外，为了澄清，我正在使用的数据库表具有唯一的发布日期。很抱歉示例图片显示了多个具有相同PublishDate的记录。

- jessegavin

---

可能是 https://dev59.com/kHVC5IYBdhLWcg3wtzqs 的重复问题。 - Drew Noakes

@Drew，你说得对。我的问题是重复的。我真的很高兴你发现了这一点！我自己找不到答案，所以才提出了这个问题。我刚刚投票关闭了这个回答。 - jessegavin

没问题。我也在尝试同样的事情，不过是通过Entity Framework（http://stackoverflow.com/questions/5382023/how-to-select-the-next-and-previous-entity-from-a-table）来实现的。将这些问题链接在一起，对于人们评估各种选项非常有用。 - Drew Noakes

Answer 1

你不能像物理上的“前一个”、“后一个”那样在SQL中使用。你需要在已排序的查询结果中找到比当前结果更大的下一个结果，例如按日期排序。这意味着你需要通过"ORDER BY"语句对结果进行排序和过滤。

- TomTom

---

在问题中，我已经说明了我正在按照唯一的PublishDate对记录进行排序。我想知道是否有可能进行1次往返到SQL服务器，选择一个ID记录以及在排序时'之前'和'之后'的记录。 - jessegavin

@jessegavin，如果您使用 UNION 将两个具有相同列的查询连接起来，则可以在单次访问数据库中完成此操作。 - Drew Noakes

Answer 2

您需要一种排序帖子的方法。如果您有这个，那么在SQL Server 2005+中，您可以像以下方式传递所需的项目号：

With OrderedPosts As
    (
    Select ...
        , ROW_NUMBER() OVER ( ORDER BY PublishDate ) As ItemRank
    From ..
    Where ...
    )
Select
From OrderedPosts
Where ItemRank = @ItemNumber

在网站代码中，你需要跟踪当前的数字，然后减去1并重新查询以获取前一个数字，或者加上1并重新查询以获取下一个数字。

如果你想在单个查询中获取当前数字的上一个和下一个数字，那么可以这样做：

With OrderedPosts As
    (
    Select ...
        , ROW_NUMBER() OVER ( ORDER BY PublishDate ) As ItemRank
    From ..
    Where ...
    )
Select
From OrderedPosts
Where ItemRank Between (@ItemNumber - 1) And (@ItemNumber + 1)

- Thomas

---

我认为这个例子适用于已经有“索引”概念的情况（例如100个位置中的第5个位置）。然而，在我的情况下，我想传递到查询中的标识符是一个GUID。例如，如果页面URL是abc.com/post.aspx?id={guid}，然后假设用户从Google结果进入该页面，那么我如何找到@ItemNumber？ - jessegavin

@jessegavin - 你必须强制实施一个序列/顺序，以便有先前和下一个的概念。如果允许用户基于某个ID访问页面，则必须有一种方法确定该项在序列中的位置，并且必须使用类似ROW_NUMBER()的东西来强制执行该序列。谷歌不允许这样做。从单个链接中无法确定给定链接是否会出现在第X页，如果用户搜索“Foo”，则必须手动确定。谷歌只是使用结果计数和起始数字来显示其结果。 - Thomas

只是想澄清一下，我并不是在建议我会依赖谷歌来管理记录索引或其他什么。我提到谷歌只是作为一个可能的来源，用户可能会从中进入我的网站的特定页面。- 我想我的问题在于，我不想先运行一个查询来确定给定GUID的@ItemNumber，以便我可以使用您上面提供的语法运行第二个查询。- 不过还是谢谢你的回答。+1 - jessegavin

Answer 3

根据Thomas的回答，您可以执行以下操作。@Id是GUID，您拥有唯一帖子。

With OrderedPosts As
    (
    Select Id
        , Title
        , PublishDate
        , ROW_NUMBER() OVER ( ORDER BY PublishDate ) As ItemRank
    From Posts
    )
Select top 3 *
From OrderedPosts
Where ItemRank > (select ItemRank from OrderedPosts where Id = @Id) - 2

- Eelze Wijma

Answer 4

请参考微软技术网站上的以下文章。

http://social.technet.microsoft.com/wiki/contents/articles/23811.paging-a-query-with-sql-server.aspx

希望这能帮到你。

- Namit