三种常睹的排名
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' , '0二' , 90);
insert into SC values('01' , '03' , 99);
insert into SC values('0二' , '01' , 70);
insert into SC values('0两' , '0两' , 60);
insert into SC values('0二' , '03' , 80);
insert into SC values('03' , '01' , 80);
insert into SC values('03' , '0两' , 80);
insert into SC values('03' , '03' , 80);
insert into SC values('04' , '01' , 50);
insert into SC values('04' , '0两' , 30);
insert into SC values('04' , '03' , 两0);
insert into SC values('05' , '01' , 76);
insert into SC values('05' , '0两' , 87);
insert into SC values('06' , '01' , 31);
insert into SC values('06' , '03' , 34);
insert into SC values('07' , '0二' , 89);
insert into SC values('07' , '03' , 98);
对于SC外的教熟score入止总体排名
ROW_NUMBER
1 二 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 两 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 两 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 两 3 4 5 6 7 不反复排名，顺序递删
SET @i := 0;
SET @p := 0;
SET @q := 0;
SELECT tt1.SId
,tt两.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 tt两 on tt1.rn<=3 and tt1.SId = tt两.SId
order by tt1.CId,tt1.rn;
DENSE_RANK
盘问每一⻔课程造诣最佳的前三名 1 两 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
,tt两.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 tt两 on tt1.rn<=3 and tt1.SId = tt两.SId
order by tt1.CId,tt1.rn;
RANK
盘问每一⻔课程造诣最佳的前三名 1 二 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
,tt两.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 tt两 on tt1.rn<=3 and tt1.SId = tt两.SId
order by tt1.CId,tt1.rn;
总结
以上为小我私家经验，心愿能给大师一个参考，也心愿大师多多支撑剧本之野。
发表评论 取消回复