mysql排名的三种常见方式

三种常见的排名

row_number、dense_rank、rank在MySQL 5.7中的实现

准备数据

表结构说明

成绩表 SC(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; 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 有重复时并列排名，最终排名也是连续的

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 有重复时并列排名，最终排名不连续

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 没有重复排名，依次递增

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 有重复时并列排名，最终排名也是连续的

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 有重复时并列排名，最终排名不连续

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;

总结

以上为个人经验，希望能给大家一个参考，也希望大家多多支持。