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

淡淡的烟草味﹌ 2023-10-07 11:21 33阅读 0赞

**rank() over,dense\_rank() over,row\_number() over****的区别**

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

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

![format_png][]

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

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

![format_png 1][]

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

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

![format_png 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;

![format_png 3][]

转载自：[https://www.cnblogs.com/scwbky/p/9558203.html][https_www.cnblogs.com_scwbky_p_9558203.html]

[format_png]: https://imgconvert.csdnimg.cn/aHR0cHM6Ly9pbWFnZXMyMDE4LmNuYmxvZ3MuY29tL2Jsb2cvMTQ3Njg4NS8yMDE4MDgvMTQ3Njg4NS0yMDE4MDgzMDA5MDUzNzkyNC02NzgyNjAwMzYucG5n?x-oss-process=image/format,png
[format_png 1]: https://imgconvert.csdnimg.cn/aHR0cHM6Ly9pbWFnZXMyMDE4LmNuYmxvZ3MuY29tL2Jsb2cvMTQ3Njg4NS8yMDE4MDgvMTQ3Njg4NS0yMDE4MDgzMDA5MDY1MDUyNi00MjQzMzU0NTMucG5n?x-oss-process=image/format,png
[format_png 2]: https://imgconvert.csdnimg.cn/aHR0cHM6Ly9pbWFnZXMyMDE4LmNuYmxvZ3MuY29tL2Jsb2cvMTQ3Njg4NS8yMDE4MDgvMTQ3Njg4NS0yMDE4MDgzMDA5MDc0NjI1MS0xMTU2OTc0MzQ1LnBuZw?x-oss-process=image/format,png
[format_png 3]: https://imgconvert.csdnimg.cn/aHR0cHM6Ly9pbWFnZXMyMDE4LmNuYmxvZ3MuY29tL2Jsb2cvMTQ3Njg4NS8yMDE4MDgvMTQ3Njg4NS0yMDE4MDgzMDA5MDgzMjYzMC04NTQyNDQ2MjIucG5n?x-oss-process=image/format,png
[https_www.cnblogs.com_scwbky_p_9558203.html]: https://www.cnblogs.com/scwbky/p/9558203.html