数据:
一、整体排名(3种)
-- 普通排名:从1开始,顺序往下排
set @rank =0;
select city ,score, @rank := @rank+1 rank
from cs order by score desc;
-- 并列排名:相同的值是相同的排名,不用占空位
set @rank=0,@price=null;
select cs.* ,
case when @price = score then @rank
when @price := score then @rank := @rank+1 end rank
from cs order by score desc;
-- 当查询的score 值 = @price时,输出@rank,
-- 当不等时,将score值赋给@price ,并输出@rank := @rank+1
-- 或者
set @rank=0,@price=null;
select a.city,a.score,a.rank from
(select cs.*,
@rank := if(@p=score,@rank,@rank+1) rank,
@p := score
from cs order by score desc) a;
-- 并列排名:相同的值是相同的排名,需要占空位
set @rank=0,@price=null, @z=1;
select a.city,a.score,a.rank from
(select cs.*,
@rank := if(@p=score,@rank,@z) rank,
@p := score,@z :=@z+1
from cs order by score desc) a;
二、分组后组内排名(3种)
-- 分组普通排名:顺序排名
set @rank=0,@c=null;
select cs.city,cs.score,
@rank := if(@c = city,@rank+1,1) rank,
@c := city
from cs order by cs.city,cs.score;
-- 组内并列排名:相同的值是相同的排名,不需要占空位
set @rank=0,@c=null,@s=null;
select cs.city,cs.score,
@rank := if(@c=city,if(@s=score,@rank,@rank+1),1) rank ,
@c := city,@s :=score
from cs order by cs.city,cs.score;
-- 组内并列排名:相同的值是相同的排名,需要占空位
set @rank=0,@z=0,@c=null,@s=null;
select cs.city,cs.score,
@z := if(@c=city,@z+1,1),
@rank := if(@c=city,if(@s=score,@rank,@z),1) rank,
@c := city,@s :=score
from cs order by cs.city,cs.score;
三、分组后取各组的前两名
-- 方法1:按照分组排名的三种方式,然后限定排名的值
set @rank=0,@z=0,@c=null,@s=null;
select a.city,a.score,a.rank from
(select cs.city city,cs.score score,
@z := if(@c=city,@z+1,1),
@rank := if(@c=city,if(@s=score,@rank,@z),1) rank,
@c := city,@s :=score
from cs order by cs.city,cs.score desc) a
where a.rank<=2;
-- 方法2:内部查询
SELECT * FROM cs c
WHERE (
SELECT count(*) FROM cs
WHERE c.city=cs.city AND c.score<cs.score )<2
ORDER BY city,score DESC;