MySQL:选择特定行之前和之后的5行 [英] MySQL: select 5 rows before and after specific row

问题描述

我有一个名为用户"的表，需要在特定行的前面和后面选择2行，并按用户排序.得分ASC

I have table called "users" and need to select 2 rows before and after specific row, sorted by users.score ASC

用户表(结构):

id     name     score
1      John       2
2      Sara       1
3      san        3
4      test       2
5      jery       5
6      simon      6
7      bob2       7
8      jack       4
9      man        2

例如:需要在users.id = 5之前和之后选择2行.users.score

for example: need to select 2 rows before and after users.id = 5 order by users.score

结果应类似于:

id     name     score
3      san        3
8      jack       4
5      jery       5
6      simon      6
7      bob2       7

谢谢

推荐答案

使用union all和子查询来限制记录应该这样做:

Using union all and subqueries to limit the records should do it:

select * from  users where id = 5
union all (
  select * from users 
  where score <  (select score from users where id = 5) 
  order by score desc limit 2
) 
union all (
  select * from users 
  where score > (select score from users where id = 5) 
  order by score asc limit 2
) 
order by score

示例SQL小提琴

我认为更好的方法是根据得分对行编号，然后从ID 5的行中选择编号为-2和+2的行:

I think a better method is to number the rows according to score and then select the rows with number -2 and +2 from the rows of id 5:

select id, name, score 
from (select 
      t.*, @rownum1 := @rownum1 + 1 as rank
      from users t, (select @rownum1 := 0) r
      order by score
     ) a,
     (select rank from (
        select t.*, 
        @rownum := @rownum + 1 as rank
        from users t, (select @rownum := 0) r
        order by score
     ) t
      where id = 5
   ) b
where b.rank between a.rank -2 and a.rank+2
order by score;    

示例SQL小提琴

这篇关于MySQL:选择特定行之前和之后的5行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！