Mysql排名，具有上下级

Question

我想使用mysql查询从表中获得范围排名。

桌子是这样的，

+------------+------+
| first_name | age  |
+------------+------+
| Kathy      |   2  |
| Jane       |   1  |
| Nick       |   3  |
| Bob        |   5  |
| Anne       |   4  |
| Jack       |   6  |
| Bill       |   8  |
| Steve      |   7  |
+------------+------+

我想得到杰克的排名，有两个排名靠后和排名靠前。

+------------+------+
| Anne       |   4  |
| Bob        |   5  |
| Jack       |   6  |
| Steve      |   7  |
| Bill       |   8  |
+------------+------+

你知道如何为MySQL数据库编写这个查询吗？

Answer 1

这是一种非常丑陋的方法，它是通过对记录应用行号来实现的。通过将行号应用于记录，您将能够在年龄不连续时返回行(使用Non-consecutive ages演示)：

select age, first_name
from 
(
  select t1.age, t1.first_name, @rownum:=@rownum+1 AS rownum
  from yourtable t1, (SELECT @rownum:=0) r
  order by t1.age
) x
where rownum  >= (select rownum
                  from 
                  (
                    select t.age, 
                      t.first_name, 
                      @rownum:=@rownum+1 AS rownum
                    from yourtable t, (SELECT @rownum:=0) r
                    order by t.age
                  ) x
                  where first_name = 'jack') - 2
 and rownum  <= (select rownum
                    from 
                    (
                      select t.age, 
                        t.first_name, 
                        @rownum:=@rownum+1 AS rownum
                      from yourtable t, (SELECT @rownum:=0) r
                      order by t.age
                    ) x
                    where first_name = 'jack') + 2;

请参阅SQL Fiddle with Demo

Answer 2

工作示例：

SELECT * FROM yourtable WHERE id BETWEEN ((SELECT id FROM yourtable WHERE name = 'Jack') - 2) AND ((SELECT id FROM yourtable WHERE name = 'Jack') + 2);

SQL Fiddle Demo