选择一行及其下一行和上一行 [英] Select a row along with its next and previous rows

问题描述

情况:

目前我有3个查询:

1. 首先-通过具有订购位置的id获取数据；
2. 第二个-按位置1获取数据；
3. 第三-按位置+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屋！