MySQL 5.7和8.0版本，不同组内排序方法——定义变量@rank 和Row_number()over(...order by (...))

【5.7版本】：定义变量

1、按每个人的利润从高到低排序，并加上排名列

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

 

2、组内排序：将每个产品在不同地区的产生利润排序

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

 

【8.0版本】：Row_number()over() 函数

1、按每个人的利润从高到低排序，并加上排名列

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

 

2、组内排序：将每个产品在不同地区的产生利润排序

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