三种常见的排名
row_number、dense_rank、rank在MySQL 5.7中的实现
准备数据
表结构说明
成绩表 SC(SId,CId,score)
- SId 学?编号
- CId 课程编号
- score 分数
创建SC表
|
1
2
3
4
5
|
create table SC(
SId varchar(10),
CId varchar(10),
score decimal(18,1)
);
|
插入数据
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
|
-- 成绩表 SC
insert into SC values('01' , '01' , 80);
insert into SC values('01' , '02' , 90);
insert into SC values('01' , '03' , 99);
insert into SC values('02' , '01' , 70);
insert into SC values('02' , '02' , 60);
insert into SC values('02' , '03' , 80);
insert into SC values('03' , '01' , 80);
insert into SC values('03' , '02' , 80);
insert into SC values('03' , '03' , 80);
insert into SC values('04' , '01' , 50);
insert into SC values('04' , '02' , 30);
insert into SC values('04' , '03' , 20);
insert into SC values('05' , '01' , 76);
insert into SC values('05' , '02' , 87);
insert into SC values('06' , '01' , 31);
insert into SC values('06' , '03' , 34);
insert into SC values('07' , '02' , 89);
insert into SC values('07' , '03' , 98);
|
对SC中的学生score进行整体排名
ROW_NUMBER
1 2 3 4 5 6 7 没有重复排名，依次递增
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
|
SET @i := 0;
SET @p := 0;
SET @q := 0;
SELECT t1.SId
,t1.CId
,t1.score
,@p := t1.score
,if(@p=@q,@i,@i := @i+1) as dense_rank
,@q :=@p
from (
SELECT SId
,CId
,score
from SC
order by score desc
) t1;
|
DENSE_RANK
1 2 3 3 3 4 5 6 7 有重复时并列排名，最终排名也是连续的
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
|
SET @i := 0;
SET @p := 0;
SET @q := 0;
SELECT t1.SId
,t1.CId
,t1.score
,@p := t1.score
,if(@p=@q,@i,@i := @i+1) as dense_rank
,@q :=@p
from (
SELECT SId
,CId
,score
from SC
order by score desc
) t1;
|
RANK
1 2 3 3 3 6 7 8 有重复时并列排名，最终排名不连续
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
|
SET @i := 0;
SET @j := 0;
SET @p := 0;
SET @q := 0;
SELECT t1.SId
,t1.CId
,t1.score
,@j := @j + 1
,@p := t1.score
,if(@p=@q,@i,@i := @j) as rank
,@q :=@p
from (
SELECT SId
,CId
,score
from SC
order by score desc
) t1;
|
进行分组排名
ROW_NUMBER
查询每?课程成绩最好的前三名 1 2 3 4 5 6 7 没有重复排名，依次递增
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
|
SET @i := 0;
SET @p := 0;
SET @q := 0;
SELECT tt1.SId
,tt2.SName
,tt1.CId
,tt1.score
,tt1.rn
from (
select t1.SId
,t1.CId
,t1.score
,@p := t1.CId
,if(@p=@q,@i := @i + 1,@i :=1) as rn
,@q := @p
from (
select SId
,CId
,score
from SC
order by CId,score DESC
) t1
) tt1 join Student tt2 on tt1.rn<=3 and tt1.SId = tt2.SId
order by tt1.CId,tt1.rn;
|
DENSE_RANK
查询每?课程成绩最好的前三名 1 2 3 3 3 4 5 6 7 有重复时并列排名，最终排名也是连续的
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
|
SET @i := 0;
SET @p := 0;
SET @q := 0;
SET @j := 0;
SET @k := 0;
SELECT tt1.SId
,tt2.SName
,tt1.CId
,tt1.score
,tt1.rn
from (
select t1.SId
,t1.CId
,t1.score
,@p := t1.CId
,@j := t1.score
,if(@p=@q,if(@j=@k,@i,@i := @i + 1),@i :=1) as rn
,@q := @p
,@k := @j
from (
select SId
,CId
,score
from SC
order by CId,score DESC
) t1
) tt1 join Student tt2 on tt1.rn<=3 and tt1.SId = tt2.SId
order by tt1.CId,tt1.rn;
|
RANK
查询每?课程成绩最好的前三名 1 2 3 3 3 6 7 8 有重复时并列排名，最终排名不连续
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
|
SET @i := 0;
SET @p := 0;
SET @q := 0;
SET @j := 0;
SET @k := 0;
SET @m := 1;
SELECT tt1.SId
,tt2.SName
,tt1.CId
,tt1.score
,tt1.rn
from (
select t1.SId
,t1.CId
,t1.score
,@p := t1.CId
,@j := t1.score
,if(@p=@q,@m := @m + 1,@m := 1)
,if(@p=@q,if(@j=@k,@i,@i := @m),@i :=1) as rn
,@q := @p
,@k := @j
from (
select SId
,CId
,score
from SC
order by CId,score DESC
) t1
) tt1 join Student tt2 on tt1.rn<=3 and tt1.SId = tt2.SId
order by tt1.CId,tt1.rn;
|
|
