在 mysql 中对结果进行排名(oracle 中 'dense_rank()' 或 'row_number()' 函数的 mysql 等价物) [英] Ranking the results in mysql (mysql equivalents for 'dense_rank()' or 'row_number()' functions in oracle)

问题描述

以下是示例数据:

dept_id  salary
10       10000
10       20000
10       20000
10       30000
20       50000
20       60000

我想对部门 ID 进行分组，然后按升序生成薪水的排名值.输出应为

I want to group the department id and then generate the rank values on salary in ascending order. The output should look as

dept_id 工资排名

dept_id salary Rank

10  10000  1
10  20000  2
10  20000  2
10  30000  3
20  50000  1
20  60000  2

我正在使用以下代码:

set @pk1 ='';
set @rn1 =1;
set @sal ='';
set @val =1;

SELECT  dept_id,
        salary,
        denseRank
FROM
(
  SELECT  dept_id,
          salary,
          @rn1 := if(@pk1=dept_id, if(@sal=salary, @rn1, @rn1+@val),1) as denseRank,
          @val := if(@pk1=dept_id, if(@sal=salary, @val+1, 1),1) as value,
          @pk1 := dept_id,
          @sal := salary     
  FROM
  (
    SELECT  dept_id,
            salary
    FROM    emp
    ORDER BY dept_id,salary
) A
) B;

这对于一小部分数据来说工作正常，但是在为整个数据(数千行)运行时会变得非常慢并且需要很长时间.有没有更好的方法或一些预定义的函数(类似于 oracle 中的dense_rank() 或row_number())来更有效地完成这项任务.

This is working fine for a small subset of data, but gets really slow and takes forever while running this for the whole data (thousands of rows). Is there a better way or some predefined function (similar to dense_rank() or row_number() in oracle) to get this task done more efficiently.

推荐答案

不知道它是否更快，但似乎打字少了很多...

Dunno if it's faster but seems like a lot less typing...

SELECT x.*
     , IF(@prev = dept_id
                , IF(salary <> @psalary,@i:=@i+1,@i:=@i)
         ,@i:=1) rank
     , @psalary := salary
     , @prev := dept_id prev 
  FROM my_table x
     , (SELECT @i:=1,@prev:='',@psalary:='') vars 
 ORDER 
    BY dept_id,salary;

这篇关于在 mysql 中对结果进行排名(oracle 中 'dense_rank()' 或 'row_number()' 函数的 mysql 等价物)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！