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