SQL-Rank-Example
数据全部使用 MySQL-Employee-Database
Rank排名(不分组)
计算薪资范围在[39200,39220]的薪资排名(数据量比较小,且包含重复值)
普通排名,不考虑值重复
使用一个@rank变量来递增排名值
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 ;
salary rk
39217 1
39217 2
39216 3
39215 4
39212 5
39206 6
39205 7
39202 8
39202 9
39201 10
39200 11
并列排名,相同值排名相同
使用一个@rank变量来递增排名值,如果和上一条记录的薪资相等,则排名不变
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;
salary rk
39217 1
39217 1
39216 2
39215 3
39212 4
39206 5
39205 6
39202 7
39202 7
39201 8
39200 9
高级并列排名,相同值排名相同,但是rank值始终递增
使用一个@irk变量来递增排名值;
同时使用另一个@rank变量来保持不变的排名值;
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;
salary rk
39217 1
39217 1
39216 3
39215 4
39212 5
39206 6
39205 7
39202 8
39202 8
39201 10
39200 11
分组Rank排名
查询这('1985-01-01 1986-01-01','1985-02-03 1986-02-03')两个时期, 各时期的薪资排名
普通排名
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
;
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
并列排名
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;
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
高级并列排名
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;
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
因为数据的问题,结果和并列排名没有区别.
