选择一行及其下一行和上一行 [英] Select a row along with its next and previous rows
本文介绍了选择一行及其下一行和上一行的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
情况:
目前我有3个查询:
- 首先-通过具有订购位置的id获取数据;
- 第二个-按位置1获取数据;
- 第三-按位置+1获取数据.
我只希望有1个查询,该查询可以通过id +上一个和下一个(如果存在)来需要一个".
I want to have only 1 Query which could take "needed one" by id + previous and next ones if they exists.
查询:
第一
set @position = 0;
SELECT
`position` FROM
(
SELECT `id`, @position:=@position+1 as `position` FROM {#table}
"other_part_of_query"
ORDER BY `modified_time` DESC
) t
WHERE
t.id = '".id."'
LIMIT 1
第二和第三
set @position = 0;
SELECT
`id` FROM
(
SELECT `id`, @position:=@position+1 as `position` FROM {#table}
"other_part_of_query"
ORDER BY `modified_time` DESC
) t
WHERE
t.`position` = '".position."'
LIMIT 1
推荐答案
这很复杂,因为您要通过id
选择一行,但是要通过另一个字段modified_time
选择相邻的行.
This is complicated, because you are selecting a row by id
, but choosing the adjacent ones by another field, modified_time
.
这个想法是使用变量来枚举行.并且,使用另一行来计算您关心的id
的值.在子查询中执行此操作,然后选择所需的行:
The idea is to use variables to enumerate the rows. And, use another row to calculate the value for the id
that you care about. Do this in a subquery, and then select the rows that you want:
SELECT t.*
FROM (SELECT `id`,
@rn := if(@rnid := if(t.id = '".id."', @rn + 1, @rnid),
@rn + 1, @rn + 1
) as rn
FROM {#table} t
"other_part_of_query" cross join
(select @rn := 0, @rnid := 0) vars
ORDER BY `modified_time` DESC
) t
WHERE rn in (@rnid - 1, @rnid, @rn)
这篇关于选择一行及其下一行和上一行的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文