MySQL:有条件地选择下一行和上一行 [英] MySQL: Conditionally selecting next and previous rows

问题描述

http://thedailywtf.com/Articles/The-Hot-Room.aspx

您会看到在底部如何找到下一篇和上一篇文章的链接("Undiveded for Divide_By_Zero"和"A完全不同的游戏")?我该如何做，但要选择下一篇和上一篇非私人文章?这适用于选择下一篇文章:

You see how at the bottom there're links to the next and previous articles ("Unprepared For Divide_By_Zero" and "A Completely Different Game")? How do I do that, but selecting the next and previous non-private articles? This works for selecting the next article:

SELECT * FROM articles WHERE id > ? AND private IS NULL

但是我找不到选择上一篇文章的方法.

But I cannot find a way to select the previous article.

最好是在一个查询中执行此操作的正确/有效方式是什么?

What is the proper/efficient way to do this, preferably in one query?

推荐答案

或扩展Jeremy的答案...
在一个查询中

Or extending Jeremy's answer...
In one query

(SELECT * FROM articles WHERE id > ? 
 AND private IS NULL 
 ORDER BY id ASC LIMIT 1) 
UNION 
(SELECT * FROM articles WHERE id < ? 
 AND private IS NULL 
 ORDER BY id DESC LIMIT 1)

这篇关于MySQL:有条件地选择下一行和上一行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！