rank() over,dense_rank() over,row_number() over的区别

Dear 丶 2021-12-05 03:11 299阅读 0赞

row\_number() over() 1 2 3 4 5 6

rank() over() 1 2 3 3 5 6

dense\_rank() over() 1 2 3 3 4 5

**1.rank() over：**查出指定条件后的进行排名。特点是，加入是对学生排名，使用这个函数，成绩相同的两名是并列，下一位同学空出所占的名次。

select name,subject,score,rank() over(partition by subject order by score desc) rankfrom student\_score;

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3NodWJpbmd6aHVveHVl_size_16_color_FFFFFF_t_70][]

**2.dense\_rank() over：**与ran() over的区别是，两名学生的成绩并列以后，下一位同学并不空出所占的名次。

select name,subject,score,dense\_rank() over(partition by subject order by score desc) rankfrom student\_score;

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3NodWJpbmd6aHVveHVl_size_16_color_FFFFFF_t_70 1][]

**3.row\_number() over**这个函数不需要考虑是否并列，哪怕根据条件查询出来的数值相同也会进行连续排名

select name,subject,score,row\_number() over(partition by subject order by score desc) rankfrom student\_score;

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3NodWJpbmd6aHVveHVl_size_16_color_FFFFFF_t_70 2][]

**4.使用rank() over**的时候，空值是最大的，如果排序字段为null,可能造成null字段排在最前面，影响排序结果。可以这样：rank() over(partition by course order by score desc nulls last)来规避这个问题。

select name,subject,score,rank() over(partition by subject order by score desc nulls last) rankfrom student\_score;

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3NodWJpbmd6aHVveHVl_size_16_color_FFFFFF_t_70 3][]

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3NodWJpbmd6aHVveHVl_size_16_color_FFFFFF_t_70]: /images/20211205/33929a841f444024aa36a77e2bd4b338.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3NodWJpbmd6aHVveHVl_size_16_color_FFFFFF_t_70 1]: /images/20211205/87ab500232a7427dbe167fd93c5df689.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3NodWJpbmd6aHVveHVl_size_16_color_FFFFFF_t_70 2]: /images/20211205/1e97e212b80b4f05a46708798a908088.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3NodWJpbmd6aHVveHVl_size_16_color_FFFFFF_t_70 3]: /images/20211205/317cc067daec4734800c1d0940cfc40c.png