如何在SQL中获取下一行 [英] How to get the next row in sql

问题描述

我有一个像这样的桌子

id  |  names  |  value  
1     Vicky       43
2     Erica       23
3     Rueben      33
4     Bob         54
5     Chris       60

然后我根据它们的值顺序设置它们.现在表看起来像这样.

Then I set them in order according to their value. Now the table looks like this.

id  |  names  |  value  
5     Chris       60
4     Bob         54
1     Vicky       43
3     Rueben      33
2     Erica       23

现在，起点是ID为5，其名称为Chris，值为60.我的目标是，获得下一行，其ID为4，名称为Bob，值为54.

Now the starting point is id 5 which has a name of Chris and a value of 60. My goal is, to get the next row which has an id of 4 and name of Bob and a value of 54.

推荐答案

您只需要限制结果集:

SELECT * from table
ORDER BY value DESC
LIMIT 1, 1

输出:

| ID | NAMES | VALUE |
|----|-------|-------|
|  4 |   Bob |    54 |

小提琴此处.

LIMIT基本上是这样工作的:第一个数字设置起始点(为0的最小值)，第二个数字设置要获取的项目数量(在这种情况下只有一个).

The LIMIT basically works this way: the first number sets the starting point (being 0 the minimal value) and the second number the amount of items to fetch (in this case only one).

理解该问题的另一种方式是:给定特定字段的值(例如，值为5的EG:id字段)...以前的记录是什么?由于数据中的ID为4，因此我们应该返回该ID.

A different way of understanding the question would be: Given a value for a particular field (EG: id field with value of 5)... what would be the previous record? As we have the id 4 in the data we should return that one.

这可以通过以下方式完成:

That could be accomplished this way:

SELECT * from t
WHERE id < 5
ORDER BY id DESC
LIMIT 1

小提琴此处.

这样，您既可以遍历两个顺序(ASC和DESC)，又可以获取下一个或上一个(>或<)行.

This way you can traverse the results in both orders (ASC and DESC) and also get both the next or previous (> or <) rows.

这篇关于如何在SQL中获取下一行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！