MySQL和PostgreSQL使用ROW_NUMBER()OVER()函数实现统计排名

业务场景：

业务比较简单，就是sql查询数据后使用order by排序，然后根据排序的结果获取每个人的排名；本来是打算在java代码中根据取出来的顺序来获取排名的，后来得知了sql中有ROW_NUMBER() OVER()这个函数，而且MySQL和PostgreSQL都有，它的本质就是将查出来的每条结果赋一个不重复的值，完美解决了这个问题；

函数格式：

ROW_NUMBER() OVER(partition by 分组列名(可选) order by 排序列名(可选))

表结构：

不加参数使用ROW_NUMBER() OVER()函数

举例：

select id, name, age, class, score ,row_number() over () as `rank` from student;

结果：

 可以看到它只是给每一行加了一个顺序数字；

这时候如果使用order by来进行排序的话就能获取到排名了

举例：

select id, name, age, class, score ,row_number() over () as `rank` from student order by score;

结果：

可以看到根据成绩得分score字段进行排序后得到的排名就是成绩的排名；

而且就算成绩一样得到的排名也不会重复，如果使用rank()函数的话，相同的成绩得到的排名是会重复的；

排序也可以写在over()里面，可以不影响原有的sql；

举例：

select id, name, age, class, score ,row_number() over (order by score) as `rank` from student;

 结果：

 可以看到结果和order by写在外面的结果是一样的；

那要是sql本来在外层就有排序的话，和over()内的排序谁优先级高呢？

举例：

select id, name, age, class, score ,row_number() over (order by score) as `rank` from student order by id;

结果：

可以看到在内外层都有排序时，得到的排名值是根据over()内部为准的；

因为over()内的分组和排序的优先级是低于外部的where 、group by、 order by命令的；

over()内除了排序外还有分组的操作，使用的命令是partition by 分组字段名

举例：

按照班级进行分组，成绩得分进行排序

select id, name, age, class, score ,row_number() over (partition by class order by score) as `rank` from student;

结果：

 可以看到排名值按照班级分成了两组，每一组都对得分成绩进行了排序；

内外层都有分组和排序

举例：

select id, name, age, class, score ,row_number() over (partition by class order by score) as `rank` from student group by id,class,age order by age;

结果：

 可以看到结果还是以over()中的分组和排序为准；

RANK() OVER()函数

举例：

select id, name, age, class, score ,rank()  over (order by score) as `rank` from student;

结果：

总结：

1.可以单独使用ROW_NUMBER() OVER()，来对每一条数据赋一个不重复的值，借助order by可以获得类似排名的值

2.over()内可以进行分组和排序，最终排名值以over()内的分组和排序为准

3.ROW_NUMBER() OVER()函数不会有重复的值，RANK() OVER()如果排序值相同的话会产生重复的排名

这篇好文章是转载于：学新通技术网