MySql - 如何获取上一行的值和下一行的值？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/20849098/
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
MySql - How get value in previous row and value in next row?
提问by Jonny 5
I have the following table, named Example:
我有下表，名为Example：
id(int 11) //not autoincriment
value (varchar 100)
It has the following rows of data:
它有以下几行数据：
0 100
2 150
3 200
6 250
7 300
Note that id values are not contiguous.
请注意，id 值不是连续的。
I've written this SQL so far:
到目前为止，我已经编写了这个 SQL：
SELECT * FROM Example WHERE id = 3
However, I don't know how to get the value of previous idand value of the next id...
但是，我不知道如何获得上一个的值id和下一个的值id...
Please help me get previous value and next value if id= 3 ?
如果id= 3，请帮助我获取上一个值和下一个值？
P.S.: in my example it will be: previous - 150, next - 250.
PS：在我的例子中，它将是：上一个 - 150，下一个 - 250。
回答by Jonny 5
Select the next row below:
选择下面的下一行：
SELECT * FROM Example WHERE id < 3 ORDER BY id DESC LIMIT 1
Select the next row above:
选择上面的下一行：
SELECT * FROM Example WHERE id > 3 ORDER BY id LIMIT 1
Select both in one query, e.g. use UNION:
在一个查询中选择两者，例如使用UNION：
(SELECT * FROM Example WHERE id < 3 ORDER BY id DESC LIMIT 1)
UNION
(SELECT * FROM Example WHERE id > 3 ORDER BY id LIMIT 1)
That what you mean?
你是那个意思？
回答by Barranka
A solution would be to use temporary variables:
一个解决方案是使用临时变量：
select
@prev as previous,
e.id,
@prev := e.value as current
from
(
select
@prev := null
) as i,
example as e
order by
e.id
To get the "next" value, repeat the procedure. Here is an example:
要获得“下一个”值，请重复该过程。下面是一个例子：
select
id, previous, current, next
from
(
select
@next as next,
@next := current as current,
previous,
id
from
(
select @next := null
) as init,
(
select
@prev as previous,
@prev := e.value as current,
e.id
from
(
select @prev := null
) as init,
example as e
order by e.id
) as a
order by
a.id desc
) as b
order by
id
Check the example on SQL Fiddle
May be overkill, but it may help you
可能有点矫枉过正，但它可能会帮助你
回答by Tin Tran
please try this sqlFiddle
请试试这个 sqlFiddle
SELECT value,
(SELECT value FROM example e2
WHERE e2.value < e1.value
ORDER BY value DESC LIMIT 1) as previous_value,
(SELECT value FROM example e3
WHERE e3.value > e1.value
ORDER BY value ASC LIMIT 1) as next_value
FROM example e1
WHERE id = 3
Edit: OP mentioned to grab value of previous idand value of next idin one of the comments so the code is here SQLFiddle
编辑：OP 提到在评论之一中获取上一个id值和下id一个值，因此代码在这里SQLFiddle
SELECT value,
(SELECT value FROM example e2
WHERE e2.id < e1.id
ORDER BY id DESC LIMIT 1) as previous_value,
(SELECT value FROM example e3
WHERE e3.id > e1.id
ORDER BY id ASC LIMIT 1) as next_value
FROM example e1
WHERE id = 3
回答by Mojtaba Rezaeian
SELECT *,
(SELECT value FROM example e1 WHERE e1.id < e.id ORDER BY id DESC LIMIT 1 OFFSET 0) as prev_value,
(SELECT value FROM example e2 WHERE e2.id > e.id ORDER BY id ASC LIMIT 1 OFFSET 0) as next_value
FROM example e
WHERE id=3;
And you can place your own offset after OFFSETkeyword if you want to select records with higher offsets for next and previous values from the selected record.
OFFSET如果您想为所选记录的下一个和上一个值选择具有更高偏移量的记录，您可以在关键字之后放置自己的偏移量。
回答by Alexander
Here's my solution may suit you:
这是我的解决方案可能适合您：
SELECT * FROM Example
WHERE id IN (
(SELECT MIN(id) FROM Example WHERE id > 3),(SELECT MAX(id) FROM Example WHERE id < 3)
)
回答by Jeff
If you do not have an ID this has worked for me.
如果你没有身，这对我有用。
Next:
下一个：
SELECT * FROM table_name
WHERE column_name > current_column_data
ORDER BY column_name ASC
LIMIT 1
Previous:
以前的：
SELECT * FROM table_name
WHERE column_name < current_column_data
ORDER BY column_name DESC
LIMIT 1
I use this for a membership list where the search is on the last name of the member. As long as you have the data from the current record it works fine.
我将它用于会员列表，其中搜索是会员的姓氏。只要您拥有当前记录中的数据，它就可以正常工作。
回答by Bohemian
This query uses a user defined variable to calculate the distance from the target id, and a series of wrapper queries to get the results you want. Only one pass is made over the table, so it should perform well.
此查询使用用户定义的变量来计算与目标 id 的距离，并使用一系列包装查询来获得您想要的结果。桌子上只有一次传球，所以它应该表现良好。
select * from (
select id, value from (
select *, (@x := ifnull(@x, 0) + if(id > 3, -1, 1)) row from (
select * from mytable order by id
) x
) y
order by row desc
limit 3
) z
order by id
See an SQLFiddle
If you don't care about the final row order you can omit the outer-most wrapper query.
如果您不关心最后的行顺序，则可以省略最外层的包装查询。
回答by peterm
A possible solution if you need it all in one row
如果您在一行中需要所有内容，则可能的解决方案
SELECT t.id, t.value, prev_id, p.value prev_value, next_id, n.value next_value
FROM
(
SELECT t.id, t.value,
(
SELECT id
FROM table1
WHERE id < t.id
ORDER BY id DESC
LIMIT 1
) prev_id,
(
SELECT id
FROM table1
WHERE id > t.id
ORDER BY id
LIMIT 1
) next_id
FROM table1 t
WHERE t.id = 3
) t LEFT JOIN table1 p
ON t.prev_id = p.id LEFT JOIN table1 n
ON t.next_id = n.id
Sample output:
示例输出：
| ID | VALUE | PREV_ID | PREV_VALUE | NEXT_ID | NEXT_VALUE | |----|-------|---------|------------|---------|------------| | 3 | 200 | 2 | 150 | 4 | 250 |
Here is SQLFiddledemo
这是SQLFiddle演示
