比较MySQL 5.7与8.0版的分组排序技巧：理解变量@rank与Row_number() over( order by ... )的区别

set @rank = 0; #定义一个变量，初始值为1；下面没查询到一个结果，变量+1，并赋值给新的字段名rank_num
select t1.*,@rank:= @rank + 1 as rank_num
from (
    select name_id,sum(profit)
    from orders
    group by name_id
    order by sum(profit)DESC) as t1

set @rank=0，@type="" #定义两个变量，一个规定排名逐渐+1，一定规定着product_id 如果改变，rank将重新赋值为1
select t2.*,
@rank:= case when @type = t2.product_id then @rank+1 else 1 end as rank_num
@type:= t2.product_id as type
from (
    select product_id,area,sum(profit)
    from orders
    group by product_id,area
    order by product_id,sum(profit) DESC) as t2

select name_id,sum(profit)，Row_number()over(order by sum(profit) DESC) as 'rank_num'
from orders
group by name_id

select product_id,area,sum(profit)，Row_number()over(partition by product_id order by sum(profit) DESC) as 'rank_num'
from orders
group by product_id,area