Mysql ranking with upper and lower rankers

Question

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

表格是这样的，

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

并且我想获得 Jack 的排名以及 2 个较低和较高的排名。

+------------+------+
| 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