定义变量实现
set @pId='', @rowNumber=0;
select
p_id,
year,
if(p_id = @pId, @rowNumber:=@rowNumber+1, @rowNumber:=1) as rowNumber,
@pId:=p_id
from
test
order by
p_id, year desc
执行结果:
窗口函数row_number()实现
MySQL从8.0开始支持窗口函数
select
p_id,
year,
row_number() over(partition by p_id order by year desc) as rowNumber
from test
执行结果:
测试表结构
p_id | year |
---|---|
101 | 2022 |
101 | 2022 |
101 | 2021 |
101 | 2020 |
102 | 2022 |
102 | 2021 |
102 | 2021 |
102 | 2021 |
102 | 2020 |
参考
MySQL :: MySQL 5.7 参考手册 :: 9.4 用户定义变量
MySQL :: MySQL 8.0 参考手册 :: 12.21.1 窗口函数说明