MySQL 在行之前选择
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/9836279/
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 select before after row
提问by bosniamaj
This is the example table:
这是示例表：
Column | 1st record | 2nd record | 3rd record | 4th record | etc<br />
id (primary) | 1 | 5 | 8 | 12 | etc<br />
name | name 1 | name 2 | name 3 | name 4 | etc<br />
date | date 1 | date 2 | date 3 | date 4 | etc<br />
callValue (unique) | val1 | val2 | val3 | val4 | etc
I select one row that is the data to show (for example: row with callValue: val3). But I cannot find a solution for this:
I need to select previous and next row. So, in this example, I need to get data from rows vallValue: val4 and callValue: val2, or id: 5 and id: 12.
我选择一行作为要显示的数据（例如：callValue 的行：val3）。但我找不到解决方案：
我需要选择上一行和下一行。因此，在此示例中，我需要从行 vallValue: val4 和 callValue: val2 或 id: 5 和 id: 12 中获取数据。
It cannot be done with id=id+-1 because iddoesn't have to be continuous because of deleting rows.
它不能用id= id+-1完成，因为id由于删除行而不必连续。
回答by Andrey Gurinov
Try this:
尝试这个：
select * from test where callValue = 'val3'
union all
(select * from test where callValue < 'val3' order by id desc limit 1)
union all
(select * from test where callValue > 'val3' order by id asc limit 1)
or
或者
select * from test where id = 8
union all
(select * from test where id < 8 order by id desc limit 1)
union all
(select * from test where id > 8 order by id asc limit 1)
回答by paxdiablo
Once you have the id 8, you should be able to do a variation on:
获得 id 后8，您应该可以对以下内容进行更改：
select * from mytable
where id < 8
order by id desc
limit 1
and:
和：
select * from mytable
where id > 8
order by id asc
limit 1
for the previous and next record.
用于上一条和下一条记录。
回答by McGarnagle
This works:
这有效：
select a.id, a.name, a.date, a.callValue
FROM
(
select @r0 := @r0 + 1 as rownum, id, name, date, callValue from TABLE
, (SELECT @r0 := 0) r0
) a,
(
select @r1 := @r1 + 1 rownum, id, name, date, callValue from TABLE
, (SELECT @r1 := 0) r1
) b
where b.callValue = val3 and b.rownum between (a.rownum-1 and a.rownum+1)
It expands the table into 2 dimensions so you can compare the rows in the fist table to any set of rows from the second.
它将表扩展为 2 个维度，因此您可以将第一个表中的行与第二个表中的任何行集进行比较。
回答by Omar Indegnos
select *
from callvalue
where id < maxd
(
select max(id) as maxd
from callvalue
where id = maxd
)
