rank() | dense_rank() | row_number() over(PARTITION BY sex order by age desc ) 的区别

1、row_num() over()函数：根据某个字段排序后编号1,2,3。。

select *,ROW_NUMBER() over ( order by majorid) as num
from StudentInfo s

 

2、rank() over(PARTITION BY 字段A order by 字段B desc ) 

根据字段A分组 每组根据字段B排名（每组中字段B值相等的话排名就相等 有并列排名时，如两个第二名 紧接着的就是第四名 跳跃的）

select *,RANK() over(PARTITION BY sex order by age desc ) as dd
from StudentManager.dbo.Student

 

3、dense_rank() over(PARTITION BY 字段A order by 字段B desc ) 和2的区别就是 即使每组内有并列排名 下个排名有不会跳跃

select *,dense_RANK() over(PARTITION BY sex order by age desc ) as dd
from StudentManager.dbo.Student

 

这三个函数over中的 order by都是必须滴 partial by可选 没有则整个结果集为一个分组。