Mysql8.0对学生成绩进行排序,并显示排名,排序是唯一的连续位次,使用变量和窗口函数两种方式实现。
##使用自定义变量rank来实现
select
sid,cid,score,
@rank:=@rank+1 as 名次
from sc,(select @rank:=0) a
order by score desc;
##使用窗口函数来实现
select
sid,cid,score,
row_number() over (order by score desc ) as 名次
from sc;
select
user_name,
goods_kind,
count(goods_kind)
row_number() over (partition by user_name order by count(goods_kind) desc)
as rk
from user_name,goods_kind;
第二步:筛选出每个用户排名第一的外卖品类
select T.user_name,T.goods_kind
(select
user_name,
goods_kind,
count(goods_kind)
row_number() over (partition by user_name order by count(goods_kind) desc)
as rk
from user_name,goods_kind
) as T where T.rk=1;