如何在 MySQL 中获取下一条/上一条记录？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1446821/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to get next/previous record in MySQL?
提问by Jakub Arnold
Say I have records with IDs 3,4,7,9 and I want to be able to go from one to another by navigation via next/previous links. The problem is, that I don't know how to fetch record with nearest higher ID.
假设我有 ID 为 3、4、7、9 的记录，我希望能够通过下一个/上一个链接导航从一个到另一个。问题是，我不知道如何获取最近的更高 ID 的记录。
So when I have a record with ID 4, I need to be able to fetch next existing record, which would be 7. The query would probably look something like
因此，当我有一个 ID 为 4 的记录时，我需要能够获取下一个现有记录，即 7。查询可能看起来像
SELECT * FROM foo WHERE id = 4 OFFSET 1
How can I fetch next/previous record without fetching the whole result set and manually iterating?
如何在不获取整个结果集并手动迭代的情况下获取下一条/上一条记录？
I'm using MySQL 5.
我正在使用 MySQL 5。
回答by longneck
next:
下一个：
select * from foo where id = (select min(id) from foo where id > 4)
previous:
以前的：
select * from foo where id = (select max(id) from foo where id < 4)
回答by Decent Dabbler
In addition to cemkalyoncu'ssolution:
除了cemkalyoncu的解决方案：
next record:
下一条记录：
SELECT * FROM foo WHERE id > 4 ORDER BY id LIMIT 1;
previous record:
之前的纪录：
SELECT * FROM foo WHERE id < 4 ORDER BY id DESC LIMIT 1;
edit:Since this answer has been getting a few upvotes lately, I really want to stress the comment I madeearlier about understanding that a primary key colum is not meant as a column to sort by, because MySQL does not guarantee that higher, auto incremented, values are necessarily added at a later time.
编辑：由于这个答案最近得到了一些赞成，我真的想强调我之前发表的关于理解主键列并不意味着作为排序依据的列的评论，因为 MySQL 不保证更高的自动递增, 值必须在以后添加。
If you don't care about this, and simply need the record with a higher (or lower) idthen this will suffice. Just don't use this as a means to determine whether a record is actually added later (or earlier). In stead, consider using a datetime column to sort by, for instance.
如果您不关心这一点，而只需要更高（或更低）的记录，id那么这就足够了。只是不要将其用作确定记录是否实际是稍后（或更早）添加的手段。相反，请考虑使用日期时间列进行排序，例如。
回答by sudip
All the above solutions require two database calls. The below sql code combine two sql statements into one.
以上所有解决方案都需要两次数据库调用。下面的 sql 代码将两条 sql 语句合二为一。
select * from foo
where (
id = IFNULL((select min(id) from foo where id > 4),0)
or id = IFNULL((select max(id) from foo where id < 4),0)
)
回答by Cem Kalyoncu
SELECT * FROM foo WHERE id>4 ORDER BY id LIMIT 1
回答by Dan
I was attempting to do something similar to this, but I needed the results ordered by date since I can't rely on the ID field as a sortable column. Here's the solution I came up with.
我试图做类似的事情，但我需要按日期排序的结果，因为我不能依赖 ID 字段作为可排序的列。这是我想出的解决方案。
First we find out the index of the desired record in the table, when it's sorted as we want:
首先我们找出表中所需记录的索引，当它按我们想要的排序时：
SELECT row
FROM
(SELECT @rownum:=@rownum+1 row, a.*
FROM articles a, (SELECT @rownum:=0) r
ORDER BY date, id) as article_with_rows
WHERE id = 50;
Then decrement the result by 2 put it in the limit statement. For example the above returned 21 for me so I run:
然后将结果减 2 放在 limit 语句中。例如，上面为我返回了 21，所以我运行：
SELECT *
FROM articles
ORDER BY date, id
LIMIT 19, 3
Gives you your primary record along with it's next and previous records given your stated order.
根据您指定的顺序，为您提供主要记录以及下一条和上一条记录。
I tried to do it as a single database call, but couldn't get the LIMIT statement to take a variable as one of it's parameters.
我试图将其作为单个数据库调用来执行，但无法让 LIMIT 语句将变量作为其参数之一。
回答by Eduardo Moralles
Using @Dan 's approach, you can create JOINs. Just use a different @variable for each sub query.
使用 @Dan 的方法，您可以创建 JOIN。只需为每个子查询使用不同的@variable。
SELECT current_row.row, current_row.id, previous_row.row, previous_row.id
FROM (
SELECT @rownum:=@rownum+1 row, a.*
FROM articles a, (SELECT @rownum:=0) r
ORDER BY date, id
) as current_row
LEFT JOIN (
SELECT @rownum2:=@rownum2+1 row, a.*
FROM articles a, (SELECT @rownum2:=0) r
ORDER BY date, id
) as previous_row ON
(current_row.id = previous_row.id) AND (current_row.row = previous_row.row - 1)
回答by Ranga Reddy
Try this example.
试试这个例子。
create table student(id int, name varchar(30), age int);
insert into student values
(1 ,'Ranga', 27),
(2 ,'Reddy', 26),
(3 ,'Vasu', 50),
(5 ,'Manoj', 10),
(6 ,'Raja', 52),
(7 ,'Vinod', 27);
SELECT name,
(SELECT name FROM student s1
WHERE s1.id < s.id
ORDER BY id DESC LIMIT 1) as previous_name,
(SELECT name FROM student s2
WHERE s2.id > s.id
ORDER BY id ASC LIMIT 1) as next_name
FROM student s
WHERE id = 7;
Note:If valueis not found then it will return null.
注意：如果没有找到值，那么它将返回null。
In the above example, Previousvalue will be Rajaand Nextvalue will be nullbecause there is no next value.
在上面的示例中， 上一个值将为Raja，下一个值将为空，因为没有下一个值。
回答by IDALICIUS
Next row
下一行
SELECT * FROM `foo` LIMIT number++ , 1
Previous row
上一行
SELECT * FROM `foo` LIMIT number-- , 1
sample next row
下一行取样
SELECT * FROM `foo` LIMIT 1 , 1
SELECT * FROM `foo` LIMIT 2 , 1
SELECT * FROM `foo` LIMIT 3 , 1
sample previous row
采样前一行
SELECT * FROM `foo` LIMIT -1 , 1
SELECT * FROM `foo` LIMIT -2 , 1
SELECT * FROM `foo` LIMIT -3 , 1
SELECT * FROM `foo` LIMIT 3 , 1
SELECT * FROM `foo` LIMIT 2 , 1
SELECT * FROM `foo` LIMIT 1 , 1
回答by magnetronnie
I had the same problem as Dan, so I used his answer and improved it.
我和丹有同样的问题，所以我使用了他的答案并改进了它。
First select the row index, nothing different here.
首先选择行索引，这里没有什么不同。
SELECT row
FROM
(SELECT @rownum:=@rownum+1 row, a.*
FROM articles a, (SELECT @rownum:=0) r
ORDER BY date, id) as article_with_rows
WHERE id = 50;
Now use two separate queries. For example if the row index is 21, the query to select the next record will be:
现在使用两个单独的查询。例如，如果行索引为 21，则选择下一条记录的查询将是：
SELECT *
FROM articles
ORDER BY date, id
LIMIT 21, 1
To select the previous record use this query:
要选择上一条记录，请使用以下查询：
SELECT *
FROM articles
ORDER BY date, id
LIMIT 19, 1
Keep in mind that for the first row (row index is 1), the limit will go to -1 and you will get a MySQL error. You can use an if-statement to prevent this. Just don't select anything, since there is no previous record anyway. In the case of the last record, there will be next row and therefor there will be no result.
请记住，对于第一行（行索引为 1），限制将变为 -1，您将收到 MySQL 错误。您可以使用 if 语句来防止这种情况。只是不要选择任何东西，因为无论如何都没有以前的记录。在最后一条记录的情况下，会有下一行，因此不会有结果。
Also keep in mind that if you use DESC for ordering, instead of ASC, you queries to select the next and previous rows are still the same, but switched.
还请记住，如果您使用 DESC 而非 ASC 进行排序，您选择下一行和上一行的查询仍然相同，但已切换。
回答by asdf2017
This is universal solution for conditions wiht more same results.
这是具有更多相同结果的条件的通用解决方案。
<?php
$your_name1_finded="somethnig searched"; //$your_name1_finded must be finded in previous select
$result = db_query("SELECT your_name1 FROM your_table WHERE your_name=your_condition ORDER BY your_name1, your_name2"); //Get all our ids
$i=0;
while($row = db_fetch_assoc($result)) { //Loop through our rows
$i++;
$current_row[$i]=$row['your_name1'];// field with results
if($row['your_name1'] == $your_name1_finded) {//If we haven't hit our current row yet
$yid=$i;
}
}
//buttons
if ($current_row[$yid-1]) $out_button.= "<a class='button' href='/$your_url/".$current_row[$yid-1]."'>BUTTON_PREVIOUS</a>";
if ($current_row[$yid+1]) $out_button.= "<a class='button' href='/$your_url/".$current_row[$yid+1]."'>BUTTON_NEXT</a>";
echo $out_button;//display buttons
?>
