sql - 具有关系的复杂 MySQL 排名查询

Question

假设我有下表:

id | name | points  
-------------------
1  | joe  | 100  
2  | bob  | 95  
3  | max  | 95  
4  | leo  | 90

我可以像这样生成反向排名记录集吗:

id | name | points | rank  
--------------------------
4  | leo  | 90     | 1  
3  | max  | 95     | 2.5  
2  | bob  | 95     | 2.5   
1  | joe  | 100    | 4  

Answer 1

这是一个完整的示例，带有示例表

create table tpoints (id int, name varchar(10), points int);
insert tpoints values
(1  ,'joe', 100 ),
(2  ,'bob', 95  ),
(3  ,'max', 95  ),
(4  ,'leo', 90  );

MySQL查询

select t.*, sq.`rank`
from
(
    select
       points,
        @rank := case when @g = points then @rank else @rn + (c-1)/2.0 end `rank`,
       @g := points,
       @rn := @rn + c
    from 
       (select @g:=null, @rn:=1) g,
        (select points, count(*) c
        from tpoints
        group by points
        order by points asc) p
) sq inner join tpoints t on t.points = sq.points
order by t.points asc;

与执行相关的交叉(自)连接相比，它还有一个好处是性能非常好。

• 1x 通过 tpoints 聚合成组
• 计算关系排名
• 1x 加入表格以根据记录排名。