MySQL 排名函数
初始数据
1、ROW_NUMBER()
序号连续但不重复;
select *,row_number() OVER(order by number ) as row_num from num
注:在使用row_number() 实现分页时需要特别注意一点,over子句中的order by 要与SQL排序记录中的order by保持一致,否则得到的序号可能不是连续的;
例如:
select *,row_number() OVER(order by number ) as row_num from num ORDER BY id
2、rank()
序号重复但不连续
select *,rank() OVER(order by number ) as row_num from num
3、dense_rank()
序号重复连续
select *,dense_rank() OVER(order by number ) as row_num from num
4、ntile()
Ntile(group_num) 将所有记录分成group_num个组,每组序号一样
select *,ntile(2) OVER(order by number ) as row_num from num