mysql排名的三种常见方式
三种常见的排名row_number、dense_rank、rank在MySQL 5.7中的实现
准备数据
表结构说明
成绩表 SC(SId,CId,score)
[*]SId 学⽣编号
[*]CId 课程编号
[*]score 分数
创建SC表
create table SC(
SId varchar(10),
CId varchar(10),
score decimal(18,1)
);
插入数据
-- 成绩表 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 没有重复排名，依次递增
SET @i := 0;
SET @p := 0;
SET @q := 0;
SELECTt1.SId
,t1.CId
,t1.score
,@p := t1.score
,if(@p=@q,@i,@i := @i+1) as dense_rank
,@q :=@p
from (
SELECTSId
,CId
,score
from SC
order by score desc
) t1;
DENSE_RANK
1 2 3 3 3 4 5 6 7 有重复时并列排名，最终排名也是连续的
SET @i := 0;
SET @p := 0;
SET @q := 0;
SELECTt1.SId
,t1.CId
,t1.score
,@p := t1.score
,if(@p=@q,@i,@i := @i+1) as dense_rank
,@q :=@p
from (
SELECTSId
,CId
,score
from SC
order by score desc
) t1;
RANK
1 2 3 3 3 6 7 8 有重复时并列排名，最终排名不连续
SET @i := 0;
SET @j := 0;
SET @p := 0;
SET @q := 0;
SELECTt1.SId
,t1.CId
,t1.score
,@j := @j + 1
,@p := t1.score
,if(@p=@q,@i,@i := @j) as rank
,@q :=@p
from (
SELECTSId
,CId
,score
from SC
order by score desc
) t1;进行分组排名
ROW_NUMBER
查询每⻔课程成绩最好的前三名 1 2 3 4 5 6 7 没有重复排名，依次递增
SET @i := 0;
SET @p := 0;
SET @q := 0;
SELECTtt1.SId
,tt2.SName
,tt1.CId
,tt1.score
,tt1.rn
from (
selectt1.SId
,t1.CId
,t1.score
,@p := t1.CId
,if(@p=@q,@i := @i + 1,@i :=1) as rn
,@q := @p
from (
selectSId
,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 有重复时并列排名，最终排名也是连续的
SET @i := 0;
SET @p := 0;
SET @q := 0;
SET @j := 0;
SET @k := 0;
SELECTtt1.SId
,tt2.SName
,tt1.CId
,tt1.score
,tt1.rn
from (
selectt1.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 (
selectSId
,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 有重复时并列排名，最终排名不连续
SET @i := 0;
SET @p := 0;
SET @q := 0;
SET @j := 0;
SET @k := 0;
SET @m := 1;
SELECTtt1.SId
,tt2.SName
,tt1.CId
,tt1.score
,tt1.rn
from (
selectt1.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 (
selectSId
,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;
总结
以上为个人经验，希望能给大家一个参考，也希望大家多多支持脚本之家。
来源:https://www.jb51.net/database/320523hnw.htm
免责声明：由于采集信息均来自互联网，如果侵犯了您的权益，请联系我们【E-Mail:cb@itdo.tech】 我们会及时删除侵权内容，谢谢合作！
页:
[1]