如何在MySQL中获取下一个/上一个记录?-Howtogetnext/previousrecordinMySQL?

201  

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)

123  

In addition to cemkalyoncu's solution:

除了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 made earlier 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) id then 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更高(或更低)的记录，那么这就足够了。只是不要把它作为一种手段来确定是否稍后(或更早)添加记录。相反，可以考虑使用datetime列进行排序。

47  

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)
      )    

15  

SELECT * FROM foo WHERE id>4 ORDER BY id LIMIT 1

9  

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代入极限语句。举个例子，上面的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语句。

5  

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 ASC LIMIT 1) as next_name
FROM student s
    WHERE id = 7; 

Note: If value is not found then it will return null.

注意:如果没有找到值，它将返回null。

In the above example, Previous value will be Raja and Next value will be null because there is no next value.

在上面的示例中，前一个值将是Raja，下一个值将为null，因为没有下一个值。

4  

Using @Dan 's approach, you can create JOINs. Just use a different @variable for each sub query.

使用@Dan的方法，您可以创建连接。只需为每个子查询使用不同的@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)

3  

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

3  

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，那么您选择下一行和前一行的查询仍然是相同的，但是已经切换了。

2  

How to get next/previous record in MySQL & PHP?

如何在MySQL和PHP中获得下一个/以前的记录?

My example is to get the id only

我的例子是只获取id

function btn_prev(){

  $id = $_POST['ids'];
  $re = mysql_query("SELECT * FROM table_name WHERE your_id <'$id'  ORDER BY your_id DESC LIMIT 1");

  if(mysql_num_rows($re) == 1)
  {
    $r = mysql_fetch_array($re);
    $ids = $r['your_id'];
    if($ids == "" || $ids == 0)
    {
        echo 0;
    }
    else
    {
        echo $ids;
    }
  }
  else
  {
    echo 0;
  }
}



function btn_next(){

  $id = $_POST['ids'];
  $re = mysql_query("SELECT * FROM table_name WHERE your_id > '$id'  ORDER BY your_id ASC LIMIT 1");

  if(mysql_num_rows($re) == 1)
  {
    $r = mysql_fetch_array($re);
    $ids = $r['your_id'];
    if($ids == "" || $ids == 0)
    {
        echo 0;
    }
    else
    {
        echo $ids;
    }
  }
  else
  {
    echo 0;
  }
}

2  

This is universal solution for conditions wiht more same results.

这是对更多相同结果的条件的普遍解。

BUTTON_PREVIOUS";
if ($current_row[$yid+1]) $out_button.= "BUTTON_NEXT";

echo $out_button;//display buttons
?>

1  

Here we have a way to fetch previous and next records using single MySQL query. Where 5 is the id of current record.

在这里，我们有一种使用单个MySQL查询获取前记录和下记录的方法。其中5是当前记录的id。

select * from story where catagory=100 and  (
    id =(select max(id) from story where id <5 and catagory=100 and order by created_at desc) 
    OR 
    id=(select min(id) from story where id > 5 and catagory=100 order by created_at desc) )

0  

There's another trick you can use to show columns from previous rows, using any ordering you want, using a variable similar to the @row trick:

您还可以使用另一个技巧来显示前几行中的列，使用任何您想要的排序，使用类似于@row技巧的变量:

SELECT @prev_col_a, @prev_col_b, @prev_col_c,
   @prev_col_a := col_a AS col_a,
   @prev_col_b := col_b AS col_b,
   @prev_col_c := col_c AS col_c
FROM table, (SELECT @prev_col_a := NULL, @prev_col_b := NULL, @prev_col_c := NULL) prv
ORDER BY whatever

Apparently, the select columns are evaluated in order, so this will first select the saved variables, and then update the variables to the new row (selecting them in the process).

显然，select列是按顺序计算的，所以这将首先选择保存的变量，然后将变量更新到新的行(在过程中选择它们)。

NB: I'm not sure that this is defined behaviour, but I've used it and it works.

NB:我不确定这是一种固定的行为，但我已经用过它了，它确实有效。

0  

If you want to feed more than one id to your query and get next_id for all of them...

如果您希望向查询提供多个id，并为所有id获取next_id……

Assign cur_id in your select field and then feed it to subquery getting next_id inside select field. And then select just next_id.

在选择字段中分配cur_id，然后将其提供给获取select字段中的next_id的子查询。然后选择next_id。

Using longneck answer to calc next_id:

使用长颈回答calc next_id:

select next_id
from (
    select id as cur_id, (select min(id) from `foo` where id>cur_id) as next_id 
    from `foo` 
) as tmp
where next_id is not null;

0  

CREATE PROCEDURE `pobierz_posty`(IN iduser bigint(20), IN size int, IN page int)
BEGIN
 DECLARE start_element int DEFAULT 0;
 SET start_element:= size * page;
 SELECT DISTINCT * FROM post WHERE id_users .... 
 ORDER BY data_postu DESC LIMIT size OFFSET start_element
END

0  

If you have an index column, say id, you can return previous and next id in one sql request. Replace :id with your value

如果您有一个索引列，比如id，您可以在一个sql请求中返回前一个和下一个id。替换:id与您的值

SELECT
 IFNULL((SELECT id FROM table WHERE id <:id ORDER BY id DESC LIMIT 1),0) as previous,
 IFNULL((SELECT id FROM table WHERE id > :id ORDER BY id ASC LIMIT 1),0) as next

0  

My solution to get the next and previews record also to get back to the first record if i'm by the last and vice versa

我的解决方案是获得下一个和预览记录，如果我是最后一个，反之亦然。

I'm not using the id i'm using the title for nice url's

我没有使用id，我用的是url的标题。

I'm using Codeigniter HMVC

我使用Codeigniter HMVC

$id = $this->_get_id_from_url($url);

//get the next id
$next_sql = $this->_custom_query("select * from projects where id = (select min(id) from projects where id > $id)");
foreach ($next_sql->result() as $row) {
    $next_id = $row->url;
}

if (!empty($next_id)) {
    $next_id = $next_id;
} else {
    $first_id = $this->_custom_query("select * from projects where id = (SELECT MIN(id) FROM projects)");
    foreach ($first_id->result() as $row) {
        $next_id = $row->url;
    }
}

//get the prev id
$prev_sql = $this->_custom_query("select * from projects where id = (select max(id) from projects where id <$id)");
foreach ($prev_sql->result() as $row) {
    $prev_id = $row->url;
}

if (!empty($prev_id)) {
    $prev_id = $prev_id;
} else {
    $last_id = $this->_custom_query("select * from projects where id = (SELECT MAX(id) FROM projects)");
    foreach ($last_id->result() as $row) {
        $prev_id = $row->url;
    }     
}

0  

Optimising @Don approach to use only One Query

优化@Don方法只使用一个查询。

SELECT * from (
  SELECT 
     @rownum:=@rownum+1 row,
     CASE a.id WHEN 'CurrentArticleID' THEN @currentrow:=@rownum ELSE NULL END as 'current_row',
     a.*  
  FROM articles a,
     (SELECT @currentrow:=0) c,  
     (SELECT @rownum:=0) r
   ORDER BY `date`, id  DESC
 ) as article_with_row
 where row > @currentrow - 2
 limit 3

change CurrentArticleID with current article ID like

用当前文章ID来更改CurrentArticleID

SELECT * from (
  SELECT 
     @rownum:=@rownum+1 row,
     CASE a.id WHEN '100' THEN @currentrow:=@rownum ELSE NULL END as 'current_row',
     a.*  
  FROM articles a,
     (SELECT @currentrow:=0) c,  
     (SELECT @rownum:=0) r
   ORDER BY `date`, id  DESC
 ) as article_with_row
 where row > @currentrow - 2
 limit 3

-1  

Select top 1 * from foo where id > 4 order by id asc

从foo中选择top 1 *， id为> 4,id为asc。

-2  

I think to have the real next or previous row in SQL table we need the real value with equal, () return more than one if you need to change position of row in a ordering table.

我认为要获得SQL表中真正的下一行或前一行，我们需要具有相等的实值，( <或> )如果需要更改排序表中的行位置，则返回多个值。

we need the value $position to search the neighbours row In my table I created a column 'position'

我们需要$position值来搜索表中的邻居行我创建了一个列" position "

and SQL query for getting the needed row is :

而获取所需行的SQL查询为:

for next :

下:

SELECT * 
FROM `my_table` 
WHERE id = (SELECT (id) 
            FROM my_table 
            WHERE position = ($position+1)) 
LIMIT 1

for previous:

前一:

 SELECT * 
 FROM my_table 
 WHERE id = (SELECT (id) 
             FROM my_table 
             WHERE `position` = ($position-1)) 
 LIMIT 1