1、row_number()
顺序排序
row_number() over (order by A asc(desc))
分组排序
row_number() over (partition by A order by B asc(desc)) 分组排序
2、rank()、dense_rank()
跳跃排序
rank() over (order by A asc(desc)) 跳跃排序 排名
顺序排序
dense_rank() over (order by A asc(desc)) 顺序排序
分组排序
dense_rank() over (partition by A order by B asc(desc)) 分组顺序排序
使用
排名第一显示“–”,固定排名第一
select
case when aa.rank = 0 then '-' else cast(aa.rank as varchar) end as rank
from (
select
dense_rank() over (order by case when a.cloumn = #{cloumn} then 0 else 1 end) -1 as rank
from table a
) aa
order by case when aa.cloumn = #{cloumn} then 0 else 1 end