博客

基础数据：department部门（字符串），employee员工（字符串），worktm工时（数字）

问题1: 从高到低输出每个员工的工时排名，工时相同按默认排序

问题2：从高到低输出每个部门共工时前三名员工，如果工时相同，名次相同，下一个员工名次连续。

问题3: 从高到低输出每个部门共工时前三名员工，如果工时相同，名次相同，下一个员工名次不连续。比如1，2，2，4

一、数据相同，序号+1

1.mysql

select department,employee,worktm,rk
    from
(
    select a.*,if(@department = department,  @i := @i + 1,@i :=1) as rk,
                     @department := department,@worktm := worktm
             from (select * from z07_test order by 1,3 desc) a ,
             (SELECT @i := 1,@Department :='',@worktm := '') as b
    ) a
order by 1,4

2.hive(row_number)

SELECT *
FROM
  (SELECT department,
          employee,
          worktm,
          row_number() over (partition BY department
                       ORDER BY worktm DESC) rk
   FROM tmp.z07_test a) a

二、数据相同，序号相同，下个等级连续

1.mysql

这里需要两层判断

select department,employee,worktm,rk
    from
(
    select a.*,if(@department = department, if(@worktm = worktm, @i := @i, @i := @i + 1),@i :=1) as rk,
                     @department := department,@worktm := worktm
             from (select * from z07_test order by 1,3 desc) a ,
             (SELECT @i := 1,@Department :='',@worktm := '') as 
    ) a
order by 1,4

2.hive(dense_rank)

SELECT *
FROM
  (SELECT department,
          employee,
          worktm,
          dense_rank() over (partition BY department
                       ORDER BY worktm DESC) rk
   FROM tmp.z07_test a) a

三、数据相同，序号相同，下个等级不连续

1.mysql（过程输出）

这里还需要赋值一个变量y，用来计算相同的次数有几次。

select a.*,if(@department = department,
                     if(@worktm = worktm,
                         @i := @i , @i := @y+1),  @i := 1 ) as rk,
        if(@department = department,@y := @y +1,  @y := 1 ) yk,
                           @department := department,@worktm := worktm,@i := @i,@y :=@y
                          from (select * from z07_test  order by 1,3 desc) a ,
                          (SELECT @i := 1,@Department :='',@worktm := '',@y := 0 )as b

2.hive(rank)

SELECT *
FROM
  (SELECT department,
          employee,
          worktm,
          rank() over (partition BY department
                       ORDER BY worktm DESC) rk
   FROM tmp.z07_test a) a