MySQL 如何在SQL中指定一条记录之后找到下一条记录？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/402980/
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 find the next record after a specified one in SQL?
提问by Ben
I'd like to use a single SQL query (in MySQL) to find the record which comes after one that I specify.
我想使用单个 SQL 查询（在 MySQL 中）来查找我指定的记录之后的记录。
I.e., if the table has:
即，如果表有：
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;
(clearly that's not real SQL syntax, I'm just using pseudo-SQL to illustrate what I'm trying to achieve)
（显然这不是真正的 SQL 语法，我只是使用伪 SQL 来说明我要实现的目标）
which would return:
这将返回：
2, pears
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?
我目前的解决方案只是获取所有记录，并在 PHP 中查看它们，但这比我想要的要慢。有没有更快的方法来做到这一点？
I'd be happy with something that returned two rows -- i.e. the one with the specified value and the following row.
我对返回两行的内容感到满意——即具有指定值的行和下一行。
EDIT: 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?
编辑：对不起，我的问题措辞不当。不幸的是，我对“下一个”的定义不是基于 ID，而是基于水果名称的字母顺序。因此，我上面的例子是错误的，应该返回橙子，因为它在苹果之后按字母顺序排列。有没有办法对字符串而不是 id 进行比较？
回答by Vinko Vrsalovic
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
回答by ólafur Waage
SELECT * FROM table WHERE id > 1 ORDER BY id LIMIT 1
Even simpler
更简单
UPDATE:
更新：
SELECT * FROM table WHERE fruit > 'apples' ORDER BY fruit LIMIT 1
回答by Charles Bretana
So simple, and no gymnastics required
如此简单，无需体操
Select * from Table
where id =
(Select Max(id) from Table
where id < @Id)
or, based on the string @fruitName = 'apples', or 'oranges' etc...
或者，基于字符串@fruitName = 'apples' 或 'oranges' 等...
Select * from Table
where id =
(Select Max(id) from Table
where id < (Select id from Table
Where fruit = @fruitName))
回答by Scott Isaacs
I'm not familiar with the MySQL syntax, but with SQL Server you can do something with "top", for example:
我不熟悉 MySQL 语法，但使用 SQL Server 你可以用“top”做一些事情，例如：
SELECT TOP 1 * FROM table WHERE id > 1 ORDER BY id;
This assumes that the id field is unique. If it is not unique (say, a foreign key), you can do something similar and then join back against the same table.
这假设 id 字段是唯一的。如果它不是唯一的（例如，外键），您可以执行类似的操作，然后重新连接同一个表。
Since I don't use MySQL, I am not sure of the syntax, but would imagine it to be similar.
由于我不使用 MySQL，我不确定语法，但会想象它是相似的。
回答by duffymo
Unless you specify a sort order, I don't believe the concepts of "previous" or "next" are available to you in SQL. You aren't guaranteed a particular order by the RDBMS by default. If you can sort by some column into ascending or descending order that's another matter.
除非您指定排序顺序，否则我认为 SQL 中您无法使用“上一个”或“下一个”的概念。默认情况下，RDBMS 不保证特定顺序。如果您可以按某列按升序或降序排序，那就是另一回事了。
回答by You're welcome.
If you are using MS SQL Server 2008(not sure if available for previous versions)...
如果您正在使用MS SQL Server 2008（不确定是否适用于以前的版本）...
In the event that you are trying to find the next record and you do not have a unique ID to reference in an applicable manner, try using ROW_NUMBER().See this link
如果您正在尝试查找下一条记录并且您没有以适用方式引用的唯一 ID，请尝试使用ROW_NUMBER().查看此链接
Depending on how savvy your T-SQLskill is, you can create row numbers based on your sorting order. Then you can find more than just the previous and next record. Utilize it in views or sub-queries to find another record relative to the current record's row number.
根据您的T-SQL技能熟练程度，您可以根据排序顺序创建行号。然后，您不仅可以找到上一条和下一条记录，还可以找到更多内容。在视图或子查询中使用它来查找与当前记录的行号相关的另一条记录。
回答by Ruuhkis
SELECT cur.id as id, nxt.id as nextId, prev.id as prevId FROM video as cur
LEFT JOIN video as nxt ON nxt.id > cur.id
LEFT JOIN video as prev ON prev.id < cur.id
WHERE cur.id = 12
ORDER BY prev.id DESC, nxt.id ASC
LIMIT 1
If you want the item with previous and next item this query lets you do just that.
如果您想要带有上一个和下一个项目的项目，此查询可让您做到这一点。
This also allows You to have gaps in the data!
这也让你在数据上有差距！
回答by tuinstoel
I don't know MySQL SQL but I still try
我不知道 MySQL SQL 但我仍然尝试
select n.id
from fruit n
, fruit p
where n.id = p.id + 1;
edit:
编辑：
select n.id, n.fruitname
from fruits n
, fruits p
where n.id = p.id + 1;
edit two:
编辑二：
Jason Lepack has said that that doesn't work when there are gaps and that is true and I should read the question better.
Jason Lepack 曾说过，当存在差距时这不起作用，这是真的，我应该更好地阅读这个问题。
I should have used analytics to sort the results on fruitname
我应该使用分析来对结果进行排序
select id
, fruitname
, lead(id) over (order by fruitname) id_next
, lead(fruitname) over (order by fruitname) fruitname_next
from fruits;
回答by JosephStyons
This should work. The string 'apples' will need to be a parameter.
这应该有效。字符串 'apples' 需要是一个参数。
Fill in that parameter with a string, and this query will return the entire record for the first fruit afterthat item, in alphabetical order.
用字符串填充该参数，此查询将按字母顺序返回该项目之后第一个水果的整个记录。
Unlike the LIMIT 1 approach, this should be platform-independent.
与 LIMIT 1 方法不同，这应该与平台无关。
--STEP THREE: Get the full record w/the ID we found in step 2
select *
from
fruits fr
,(
--STEP TWO: Get the ID # of the name we found in step 1
select
min(vendor_id) min_id
from
fruits fr1
,(
--STEP ONE: Get the next name after "apples"
select min(name) next_name
from fruits frx
where frx.name > 'apples'
) minval
where fr1.name = minval.next_name
) x
where fr.vendor_id = x.min_id;
The equivalent to the LIMIT 1 approach in Oracle (just for reference) would be this:
相当于 Oracle 中的 LIMIT 1 方法（仅供参考）是这样的：
select *
from
(
select *
from fruits frx
where frx.name > 'apples'
order by name
)
where rownum = 1
回答by doug
How about this:
这个怎么样：
Select * from table where id = 1 + 1
