mysql - 如何在mysql中获取这个特定的用户排名查询？

Question

我的用户数据库中有 tbl_items，我想对具有特定 ID (514) 的特定项目的用户排名进行排序。我在我的开发环境中有这组数据的测试数据:

mysql> select * from tbl_items where classid=514;
+---------+---------+----------+
| ownerId | classId | quantity |
+---------+---------+----------+
|       1 |     514 |        3 |
|       2 |     514 |        5 |
|       3 |     514 |       11 |
|       4 |     514 |       46 |
|       5 |     514 |       57 |
|       6 |     514 |        6 |
|       7 |     514 |        3 |
|       8 |     514 |       27 |
|      10 |     514 |        2 |
|      11 |     514 |       73 |
|      12 |     514 |       18 |
|      13 |     514 |       31 |
+---------+---------+----------+
12 rows in set (0.00 sec)

到目前为止一切顺利 :) 我编写了以下查询:

set @row=0;

select a.*, @row:=@row+1 as rank 
from (select a.ownerid,a.quantity from tbl_items a 
where a.classid=514) a order by quantity desc;

+---------+----------+------+
| ownerid | quantity | rank |
+---------+----------+------+
|      11 |       73 |    1 |
|       5 |       57 |    2 |
|       4 |       46 |    3 |
|      13 |       31 |    4 |
|       8 |       27 |    5 |
|      12 |       18 |    6 |
|       3 |       11 |    7 |
|       6 |        6 |    8 |
|       2 |        5 |    9 |
|       7 |        3 |   10 |
|       1 |        3 |   11 |
|      10 |        2 |   12 |
+---------+----------+------+
12 rows in set (0.00 sec)

对用户进行正确排名。但是在有很多记录的表中，我需要执行以下操作:

1) 能够获取列表的一小部分，围绕用户排名实际所在的位置，这将使我获得周围的记录，从而保留总体排名:我尝试通过将用户变量设置为当前用户的排名并使用偏移量和限制来完成这些操作，但无法保留整体排名。

这应该让我得到类似下面的东西(例如 ownerId=2 和环境限制 5:

+---------+----------+------+
| ownerid | quantity | rank |
+---------+----------+------+
|       3 |       11 |    7 |
|       6 |        6 |    8 |
|       2 |        5 |    9 | --> ownerId=2
|       7 |        3 |   10 |
|       1 |        3 |   11 |
+---------+----------+------+
5 rows in set (0.00 sec)

2) 我还需要另一个查询(最好是单个查询)让我获得前 3 名 + 具有特定 id 的特定用户的排名，最好是单个查询，无论他是否名列前茅3个地方与否。我也听不懂

它看起来像下面这样(例如再次 ownerId=2):

+---------+----------+------+
| ownerid | quantity | rank |
+---------+----------+------+
|      11 |       73 |    1 |
|       5 |       57 |    2 |
|       4 |       46 |    3 |
|       2 |        5 |    9 | --> ownerId=2
+---------+----------+------+
4 rows in set (0.00 sec)

此外，我有点担心在具有数百万条记录的表上的查询性能......希望有人帮忙:)

Answer 1

1) 围绕给定 id 的 5 个条目。

set @row=0;
set @rk2=-1;
set @id=2;

select b.* from (
  select a.*, @row:=@row+1 as rank, if(a.ownerid=@id, @rk2:=@row, -1) as rank2 
  from (
    select a.ownerid,a.quantity
     from tbl_items a 
    where a.classid=514) a
   order by quantity desc) b
where b.rank > @rk2 - 3
limit 5;

尽管您会得到一个额外的列 rank2:您可能希望通过明确的列列表而不是 b.* 将其过滤掉。也许可以使用 having 子句而不是额外的嵌套。

2) 3 个排名靠前的条目 + 1 个特定的 id

select b.* from (
  select a.*, @row:=@row+1 as rank 
  from (
    select a.ownerid,a.quantity
    from tbl_items a 
    where a.classid=514) a
  order by quantity desc) b
where b.rank < 4 or b.ownerid=@id