在MySQL中通过分区排名() [英] Rank() over Partition by in mysql

问题描述

我完全无法在mysql中从rank()over(按x分区，按y desc排序)创建新列"LoginRank".

I'm completely stumped as to create a new column "LoginRank" from rank() over(partition by x, order by y desc) in mysql.

在sql server中，我将编写以下查询，以创建"Loginrank"列，该列按"login"分组并按"id"排序.

From sql server i would write the following query, to create a column "Loginrank" that is grouped by "login" and ordered by "id".

select ds.id, 
       ds.login, 
       rank() over(partition by ds.login order by ds.id asc) as LoginRank
from tablename.ds

我有下表.

create table ds (id int(11), login int(11))
insert into ds (id, login) 
values  (1,1),
    (2,1),
    (3,1),
    (4,2),
    (5,2),
    (6,6),
    (7,6),
    (8,1)   

我尝试将许多现有的mysql修复程序应用于数据集，但仍然存在问题.

I tried applying many existing mysql fixes to my dataset but continue to have issues.

任何帮助将不胜感激. 谢谢！

Any help is greatly appreciated. Thanks!

推荐答案

尝试以下查询:-MySql不支持Rank()函数.

Try this query: - MySql does not support Rank() function.

select result.id,result.login,result.rank from (
  SELECT    id,
              login,
              IF(login=@last,@curRank:=@curRank,@curRank:=@_sequence) AS rank,
              @_sequence:=@_sequence+1,
              @last:=login
    FROM      ds , (SELECT @curRank := 1, @_sequence:=1, @last:=0) r
    ORDER BY  id asc) as result;

希望它对您有帮助！

这篇关于在MySQL中通过分区排名()的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！