SELECT

    year,

    id,

    score,

    DENSE_RANK() OVER (PARTITION BY year ORDER BY score DESC) as rank_within_year

FROM

    your_table

ORDER BY

    year,

    rank_within_year,

    score DESC;

在这个查询中：

year是您想要分区的列，它表示数据的年份。

score是您想要根据其排序的列。

DENSE_RANK()函数在OVER子句中使用了PARTITION BY year，这意味着它会为每个year值创建一个分区，并在每个分区内根据score的降序排列生成序号。

oracle开窗函数的分组排序，over里面加partition by 分组，

over(partition by 年份 order by 分数)

with aa as

 (select '2020' as accyear, 99 as nnum from dual

  union all

  select '2021' as accyear, 88 as nnum

    from dual

  union all

  select '2022' as accyear, 92 as nnum

    from dual

  union all

  select '2023' as accyear, 97 as nnum

    from dual)

select k.accyear, k.nnum, dense_rank() over(order by nnum desc) as 序号

  from aa k