SQL-Rank-Example

数据全部使用 MySQL-Employee-Database

Rank排名(不分组)

计算薪资范围在[39200,39220]的薪资排名(数据量比较小,且包含重复值)

普通排名,不考虑值重复

使用一个@rank变量来递增排名值

<code class="language-sql">select s.salary, @rank:=@rank+1 as rk 
from salaries s,(select @rank:=0) r 
where 
s.salary between 39200 and 39220 
order by s.salary desc ;
</code>

<code>salary
rk
39217
1
39217
2
39216
3
39215
4
39212
5
39206
6
39205
7
39202
8
39202
9
39201
10
39200
11
</code>

并列排名,相同值排名相同

使用一个@rank变量来递增排名值,如果和上一条记录的薪资相等,则排名不变

<code class="language-sql">select s.salary,
case 
when @last_s = s.salary then @rank
when @last_s:=s.salary then @rank:=@rank+1
end as rk
from salaries s,(select @rank:=0,@last_s:=0) t 
where 
s.salary between 39200 and 39220 
order by s.salary desc ;
#-------------------------------
#另一种IF的写法
select salary,rk from
(
select s.salary,
@rank:=if(@last_s = s.salary,@rank,@rank+1) as rk,
@last_s:=s.salary
from salaries s,(select @rank:=0,@last_s:=0) t 
where 
s.salary between 39200 and 39220 
order by s.salary desc
) r;
</code>

<code>salary
rk
39217
1
39217
1
39216
2
39215
3
39212
4
39206
5
39205
6
39202
7
39202
7
39201
8
39200
9
</code>

高级并列排名,相同值排名相同,但是rank值始终递增

使用一个@irk变量来递增排名值;
同时使用另一个@rank变量来保持不变的排名值;

<code class="language-sql">select salary , rk from
(
select s.salary,
@rank:=if(@last_s=s.salary,@rank,@irk+1) as rk,
@irk:=@irk+1,
@last_s:=s.salary
from salaries s,(select @rank:=0,@irk:=0,@last_s:=0) t
where 
s.salary between 39200 and 39220 
order by s.salary desc 
) s;
</code>

<code>salary
rk
39217
1
39217
1
39216
3
39215
4
39212
5
39206
6
39205
7
39202
8
39202
8
39201
10
39200
11
</code>

分组Rank排名

查询这(‘1985-01-01 1986-01-01‘,‘1985-02-03 1986-02-03‘)两个时期, 各时期的薪资排名

普通排名

<code class="language-sql">SELECT 
emp_no, 
period , 
salary ,
if(@pre_t=period,@rank:=@rank+1,@rank:=1) as rk,
@pre_t:=period
FROM 
 (
  SELECT emp_no, 
  concat(from_date,‘ ‘,to_date) period , 
  salary
  FROM salaries 
 ) t1 , 
    (select @rank:=0,@pre_t:=0) t2
where 
 period in (‘1985-01-01 1986-01-01‘,‘1985-02-03 1986-02-03‘)
order by period , salary desc
;
</code>

<code>period,salary,rk
"1985-01-01 1986-01-01",72446,1
"1985-01-01 1986-01-01",71612,2
"1985-01-01 1986-01-01",71166,3
"1985-01-01 1986-01-01",61357,4
"1985-01-01 1986-01-01",60026,5
"1985-01-01 1986-01-01",48626,6
"1985-01-01 1986-01-01",48291,7
"1985-01-01 1986-01-01",42093,8
"1985-01-01 1986-01-01",40000,9
"1985-02-03 1986-02-03",90217,1
"1985-02-03 1986-02-03",88375,2
"1985-02-03 1986-02-03",87439,3
"1985-02-03 1986-02-03",84359,4
"1985-02-03 1986-02-03",76874,5
"1985-02-03 1986-02-03",76683,6
"1985-02-03 1986-02-03",75713,7
"1985-02-03 1986-02-03",75105,8
"1985-02-03 1986-02-03",71024,9
...
"1985-02-03 1986-02-03",40000,61
</code>

并列排名

<code class="language-sql">select period,salary,rk from (
SELECT 
period , 
salary ,
if(@pre_t=period,if(@pre_s=salary,@rank,@rank:=@rank+1),@rank:=1) as rk,
@pre_t:=period,
@pre_s:=salary
FROM 
 (
  SELECT emp_no, 
  concat(from_date,‘ ‘,to_date) period , 
  salary
  FROM salaries 
 ) t1 , 
    (select @rank:=0,@pre_t:=0,@pre_s:=0) t2
where 
 period in (‘1985-01-01 1986-01-01‘,‘1985-02-03 1986-02-03‘)
order by period , salary desc 
) ft;
</code>

<code>period,salary,rk
"1985-01-01 1986-01-01",72446,1
"1985-01-01 1986-01-01",71612,2
"1985-01-01 1986-01-01",71166,3
"1985-01-01 1986-01-01",61357,4
"1985-01-01 1986-01-01",60026,5
"1985-01-01 1986-01-01",48626,6
"1985-01-01 1986-01-01",48291,7
"1985-01-01 1986-01-01",42093,8
"1985-01-01 1986-01-01",40000,9
"1985-02-03 1986-02-03",90217,1
"1985-02-03 1986-02-03",88375,2
"1985-02-03 1986-02-03",87439,3
"1985-02-03 1986-02-03",84359,4
"1985-02-03 1986-02-03",76874,5
"1985-02-03 1986-02-03",76683,6
"1985-02-03 1986-02-03",75713,7
"1985-02-03 1986-02-03",75105,8
"1985-02-03 1986-02-03",71024,9
"1985-02-03 1986-02-03",66319,10
"1985-02-03 1986-02-03",66138,11
...
"1985-02-03 1986-02-03",40000,39
"1985-02-03 1986-02-03",40000,39
"1985-02-03 1986-02-03",40000,39
"1985-02-03 1986-02-03",40000,39
"1985-02-03 1986-02-03",40000,39
"1985-02-03 1986-02-03",40000,39
"1985-02-03 1986-02-03",40000,39
"1985-02-03 1986-02-03",40000,39
</code>

高级并列排名

<code class="language-sql">select period,salary,rk from (
SELECT 
period , 
salary ,
if(@pre_t=period,@irk:=@irk+1,@irk:=1) as irk,
if(@pre_t=period,if(@pre_s=salary,@rank,@rank:=@irk),@rank:=@irk) as rk,
@pre_t:=period,
@pre_s:=salary
FROM 
 (
  SELECT emp_no, 
  concat(from_date,‘ ‘,to_date) period , 
  salary
  FROM salaries 
 ) t1 , 
    (select @rank:=0,@pre_t:=0,@pre_s:=0,@irk:=0) t2
where 
 period in (‘1985-01-01 1986-01-01‘,‘1985-02-03 1986-02-03‘)
order by period , salary desc 
) ft;
</code>

<code>period,salary,rk
"1985-01-01 1986-01-01",72446,1
"1985-01-01 1986-01-01",71612,2
"1985-01-01 1986-01-01",71166,3
"1985-01-01 1986-01-01",61357,4
"1985-01-01 1986-01-01",60026,5
"1985-01-01 1986-01-01",48626,6
"1985-01-01 1986-01-01",48291,7
"1985-01-01 1986-01-01",42093,8
"1985-01-01 1986-01-01",40000,9
"1985-02-03 1986-02-03",90217,1
"1985-02-03 1986-02-03",88375,2
"1985-02-03 1986-02-03",87439,3
"1985-02-03 1986-02-03",84359,4
"1985-02-03 1986-02-03",76874,5
"1985-02-03 1986-02-03",76683,6
"1985-02-03 1986-02-03",75713,7
"1985-02-03 1986-02-03",75105,8
"1985-02-03 1986-02-03",71024,9
"1985-02-03 1986-02-03",66319,10
"1985-02-03 1986-02-03",66138,11
...
"1985-02-03 1986-02-03",40000,39
"1985-02-03 1986-02-03",40000,39
"1985-02-03 1986-02-03",40000,39
"1985-02-03 1986-02-03",40000,39
"1985-02-03 1986-02-03",40000,39
"1985-02-03 1986-02-03",40000,39
"1985-02-03 1986-02-03",40000,39
"1985-02-03 1986-02-03",40000,39
</code>

因为数据的问题,结果和并列排名没有区别.

SQL-Rank-Example

标签：com   数据   ref   包含   data   http   end   select   desc