如何在SQL中指定的一条记录后查找下一条记录? [英] How to find the next record after a specified one in SQL?

问题描述

我想使用一个SQL查询(在MySQL中)查找我指定的记录之后的记录.

I'd like to use a single SQL query (in MySQL) to find the record which comes after one that I specify.

即，如果表具有:

id, fruit
--  -----
1   apples
2   pears
3   oranges

我希望能够执行以下查询:

I'd like to be able to do a query like:

SELECT * FROM table where previous_record has id=1 order by id;

(显然这不是真正的SQL语法，我只是在使用伪SQL来说明我要实现的目标)

(clearly that's not real SQL syntax, I'm just using pseudo-SQL to illustrate what I'm trying to achieve)

将返回:

2, pears

我当前的解决方案只是获取所有记录，并在PHP中浏览它们，但这比我想要的要慢.有更快的方法吗?

My current solution is just to fetch all the records, and look through them in PHP, but that's slower than I'd like. Is there a quicker way to do it?

我对返回两行的东西感到满意-即具有指定值的那一行和下一行.

I'd be happy with something that returned two rows -- i.e. the one with the specified value and the following row.

对不起，我的问题措辞不好.不幸的是，我对下一个"的定义不是基于ID，而是基于水果名称的字母顺序.因此，我上面的示例是错误的，应该返回橘子，因为它按字母顺序排在苹果之后.有没有办法对字符串而不是ID进行比较?

Sorry, my question was badly worded. Unfortunately, my definition of "next" is not based on ID, but on alphabetical order of fruit name. Hence, my example above is wrong, and should return oranges, as it comes alphabetically next after apples. Is there a way to do the comparison on strings instead of ids?

推荐答案

在对问题进行了编辑和下面的简化之后，我们可以将其更改为

After the question's edit and the simplification below, we can change it to

SELECT id FROM table WHERE fruit > 'apples' ORDER BY fruit LIMIT 1

这篇关于如何在SQL中指定的一条记录后查找下一条记录?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！