如何在MySQL中获取下一个/上一个记录？-Java 学习之路

110

假设我有ID 3,4,7,9的记录，我希望能够通过下一个/上一个链接导航从一个到另一个 . 问题是，我不知道如何获取最近的更高ID的记录 .

因此，当我有一个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?

我正在使用MySQL 5 .

20 回答

下一个：

select * from foo where id = (select min(id) from foo where id > 4)

以前：

select * from foo where id = (select max(id) from foo where id < 4)

回复于 2024-05-02T23:06:59+08:00

127
除了cemkalyoncu's解决方案：

下一条记录：
```
SELECT * FROM foo WHERE id > 4 ORDER BY id LIMIT 1;
```
之前的纪录：
```
SELECT * FROM foo WHERE id < 4 ORDER BY id DESC LIMIT 1;
```
edit: 由于这个答案最近得到了一些支持，我真的想强调comment I made早先关于理解主键列不是要排序的列，因为MySQL不保证更高的自动递增值是必须在以后添加 .

如果你不关心这个，只需要更高（或更低） id 的记录，那么这就足够了 . 只是不要将其用作确定记录是否实际在以后（或更早）添加的方法 . 例如，请考虑使用datetime列进行排序 .
回复于 2024-05-02T23:06:59+08:00

212

以上所有解决方案都需要两次数据库调用下面的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)
      )

回复于 2024-05-02T23:06:59+08:00

-2

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

回复于 2024-05-02T23:06:59+08:00

1
我试图做类似的事情，但我需要按日期排序的结果，因为我不能依赖ID字段作为可排序的列 . 这是我提出的解决方案 .

首先，我们根据需要对表中所需记录的索引进行排序：
```
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;
```
然后将结果减2，将其放入限制语句中 . 例如，以上为我返回了21，所以我运行：
```
SELECT * 
FROM articles
ORDER BY date, id
LIMIT 19, 3
```
根据您所声明的订单，为您提供主要记录以及下一个和之前的记录 .

我尝试将其作为单个数据库调用来执行，但无法获取LIMIT语句将变量作为其中一个参数 .
回复于 2024-05-02T23:06:59+08:00

试试这个例子 .

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: 如果未找到 value ，则返回 null .

在上面的示例中， Previous 值将为 Raja ， Next 值将为 null ，因为没有下一个值 .

回复于 2024-05-02T23:06:59+08:00

下一行

SELECT * FROM `foo` LIMIT number++ , 1

上一行

SELECT * FROM `foo` LIMIT number-- , 1

下一行示例

SELECT * FROM `foo` LIMIT 1 , 1
SELECT * FROM `foo` LIMIT 2 , 1
SELECT * FROM `foo` LIMIT 3 , 1

上一行示例

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

回复于 2024-05-02T23:06:59+08:00

使用@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)

回复于 2024-05-02T23:06:59+08:00

4
我和Dan有同样的问题，所以我用他的答案改进了它 .

首先选择行索引，这里没什么不同 .
```
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;
```
现在使用两个单独的查询 . 例如，如果行索引是21，则选择下一条记录的查询将是：
```
SELECT * 
FROM articles
ORDER BY date, id
LIMIT 21, 1
```
要选择以前的记录，请使用以下查询：
```
SELECT * 
FROM articles
ORDER BY date, id
LIMIT 19, 1
```
请记住，对于第一行（行索引为1），限制将变为-1，您将收到MySQL错误 . 您可以使用if语句来防止这种情况 . 只是不要选择任何东西，因为无论如何都没有以前的记录 . 在最后一条记录的情况下，将有下一行，因此将没有结果 .

另外请记住，如果您使用DESC进行排序，而不是ASC，则查询选择下一行和前一行仍然相同，但是已切换 .
回复于 2024-05-02T23:06:59+08:00

-2

这是具有更多相同结果的条件的通用解决方案 .

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

回复于 2024-05-02T23:06:59+08:00

如何获得MySQL和PHP的下一个/上一个记录？

我的例子是只获取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;
  }
}

回复于 2024-05-02T23:06:59+08:00

优化@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

使用当前文章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

回复于 2024-05-02T23:06:59+08:00

在这里，我们有一种方法可以使用单个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) )

回复于 2024-05-02T23:06:59+08:00

3
您可以使用类似于@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
```
显然，选择列按顺序进行评估，因此首先选择已保存的变量，然后将变量更新为新行（在过程中选择它们） .

注意：我不确定这是定义的行为，但我已经使用它并且它有效 .
回复于 2024-05-02T23:06:59+08:00
0
如果你想查询 feed more than one id 并获得 next_id 所有这些...

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

使用longneck回答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;
```
回复于 2024-05-02T23:06:59+08:00

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

回复于 2024-05-02T23:06:59+08:00

如果你有一个索引列，比如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

回复于 2024-05-02T23:06:59+08:00

如果我是最后一个，那么我获得下一个和预览记录的解决方案也会回到第一条记录，反之亦然

我没有使用id我正在使用 Headers 为漂亮的网址

我正在使用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;
    }     
}

回复于 2024-05-02T23:06:59+08:00

-1

从foo中选择top 1 *，其中id> 4 order by id asc

回复于 2024-05-02T23:06:59+08:00
2
我想在SQL表中有真正的下一行或前一行我们需要相等的实数值，（<或>）如果需要在排序表中更改行的位置，则返回多个 .

我们需要值 $position 来搜索 neighbours 行在我的表中我创建了一个列'position'

和获取所需行的SQL查询是：

下一个：
```
SELECT * 
FROM `my_table` 
WHERE id = (SELECT (id) 
            FROM my_table 
            WHERE position = ($position+1)) 
LIMIT 1
```
对于上一个：
```
SELECT * 
 FROM my_table 
 WHERE id = (SELECT (id) 
             FROM my_table 
             WHERE `position` = ($position-1)) 
 LIMIT 1
```
回复于 2024-05-02T23:06:59+08:00

如何在MySQL中获取下一个/上一个记录？

20 回答

相关问题