Mysql窗口函数排序

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;