各位用户为了找寻关于postgresql rank() over, dense_rank(), row_number()用法区别的资料费劲了很多周折。这里教程网为您整理了关于postgresql rank() over, dense_rank(), row_number()用法区别的相关资料，仅供查阅，以下为您介绍关于postgresql rank() over, dense_rank(), row_number()用法区别的详细内容

如下学生表student，学生表中有姓名、分数、课程编号，需要按照课程对学生的成绩进行排序

? 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 select * from jinbo.student;  id | name | score | course ----+-------+-------+--------  5 | elic | 70 |  1  4 | dock | 100 |  1  3 | cark | 80 |  1  2 | bob | 90 |  1  1 | alice | 60 |  1  10 | jacky | 80 |  2  9 | iris | 80 |  2  8 | hill | 60 |  1  7 | grace | 50 |  2  6 | frank | 70 |  2  6 | test |  |  2 (11 rows)

1、rank over () 可以把成绩相同的两名是并列，如下course = 2 的结果rank值为：1 2 2 4 5

? 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 select name,   score,   course,   rank() over(partition by course order by score desc) as rank  from jinbo.student;  name | score | course | rank -------+-------+--------+------  dock | 100 |  1 | 1  bob | 90 |  1 | 2  cark | 80 |  1 | 3  elic | 70 |  1 | 4  hill | 60 |  1 | 5  alice | 60 |  1 | 5  test |  |  2 | 1  iris | 80 |  2 | 2  jacky | 80 |  2 | 2  frank | 70 |  2 | 4  grace | 50 |  2 | 5 (11 rows)

2、dense_rank()和rank over()很相似，可以把学生成绩并列不间断顺序排名，如下course = 2 的结果rank值为：1 2 2 3 4

? 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 select name,score,   course,   dense_rank() over(partition by course order by score desc) as rank  from jinbo.student;  name | score | course | rank -------+-------+--------+------  dock | 100 |  1 | 1  bob | 90 |  1 | 2  cark | 80 |  1 | 3  elic | 70 |  1 | 4  hill | 60 |  1 | 5  alice | 60 |  1 | 5  test |  |  2 | 1  iris | 80 |  2 | 2  jacky | 80 |  2 | 2  frank | 70 |  2 | 3  grace | 50 |  2 | 4 (11 rows)

3、row_number 可以把相同成绩的连续排名，如下 course = 2 的结果rank值为：1 2 3 4 5

? 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 select name,score,   course,   row_number() over(partition by course order by score desc) as rank  from jinbo.student;  name | score | course | rank -------+-------+--------+------  dock | 100 |  1 | 1  bob | 90 |  1 | 2  cark | 80 |  1 | 3  elic | 70 |  1 | 4  hill | 60 |  1 | 5  alice | 60 |  1 | 6  test |  |  2 | 1  iris | 80 |  2 | 2  jacky | 80 |  2 | 3  frank | 70 |  2 | 4  grace | 50 |  2 | 5 (11 rows)

使用rank over()的时候，空值是最大的，如果排序字段为null, 可能造成null字段排在最前面，影响排序结果，可以如下：

rank over(partition by course order by score desc nulls last)

4、总结

partition by 用于结果集分组，如果没有指定，会把整个结果集作为一个分组

rank 、dense_rank 、row_numer 都是不同方式的结果集组内排序，一般都结合over 字句出现，over 字句里 会有 partition by、order by、last、first 的任意组合，如下：

? 1 2 3 rank() over(partition by a,b order by a, order by b desc); rank() over(partition by a order by b nulls first) rank() over(partition by a order by b nulls last)

补充：Oracle或者PostgreSQL的row_number over 排名语法

PostgreSQL 和Oracle 都提供了 row_number() over() 这样的语句来进行对应的字段排名，很是方便。MySQL却没有提供这样的语法。

这次我提供的表结构如下，

? 1 2 3 4 5     Table "ytt.t1"  Column |   Type   | Modifiers --------+-----------------------+-----------  i_name | character varying(10) | not null  rank | integer    | not null

我模拟了20条数据来做演示。

? 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 t_girl=# select * from t1 order by i_name;         i_name | rank ---------+------  Charlie | 12  Charlie | 12  Charlie | 13  Charlie | 10  Charlie | 11  Lily  | 6  Lily  | 7  Lily  | 7  Lily  | 6  Lily  | 5  Lily | 7  Lily | 4  Lucy | 1  Lucy | 2  Lucy | 2  Ytt  | 14  Ytt  | 15  Ytt  | 14  Ytt  | 14  Ytt  | 15 (20 rows)

在PostgreSQL下，我们来对这样的排名函数进行三种不同的执行方式1：

第一种：

完整的带有排名字段以及排序。

? 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 t_girl=# select i_name,rank, row_number() over(partition by i_name order by rank desc) as rank_number from t1;   i_name | rank | rank_number ---------+------+-------------  Charlie  | 13 |   1  Charlie | 12 |   2  Charlie | 12 |   3  Charlie | 11 |   4  Charlie | 10 |   5  Lily  | 7 |   1  Lily  | 7 |   2  Lily  | 7 |   3  Lily  | 6 |   4  Lily  | 6 |   5  Lily  | 5 |   6  Lily  | 4 |   7  Lucy | 2 |   1  Lucy | 2 |   2  Lucy | 1 |   3  Ytt  | 15 |   1  Ytt  | 15 |   2  Ytt  | 14 |   3  Ytt  | 14 |   4  Ytt  | 14 |   5 (20 rows)

第二种：

带有完整的排名字段但是没有排序。

? 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 t_girl=# select i_name,rank, row_number() over(partition by i_name ) as rank_number from t1;  i_name | rank | rank_number ---------+------+-------------  Charlie  | 12 |   1  Charlie | 12 |   2  Charlie | 13 |   3  Charlie | 10 |   4  Charlie | 11 |   5  Lily  | 6 |   1  Lily  | 7 |   2  Lily  | 7 |   3  Lily  | 6 |   4  Lily  | 5 |   5  Lily  | 7 |   6  Lily  | 4 |   7  Lucy | 1 |   1  Lucy | 2 |   2  Lucy | 2 |   3  Ytt  | 14 |   1  Ytt  | 15 |   2  Ytt  | 14 |   3  Ytt  | 14 |   4  Ytt  | 15 |   5 (20 rows)

第三种：

没有任何排名字段，也没有任何排序字段。

? 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 t_girl=# select i_name,rank, row_number() over() as rank_number from t1;  i_name | rank | rank_number ---------+------+-------------  Lily  | 7 |   1  Lucy | 2 |   2  Ytt  | 14 |   3  Ytt  | 14 |   4  Charlie | 12 |   5  Charlie | 13 |   6  Lily  | 7 |   7  Lily  | 4 |   8  Ytt  | 14 |   9  Lily  | 6 |   10  Lucy | 1 |   11  Lily  | 7 |   12  Ytt  | 15 |   13  Lily  | 6 |   14  Charlie | 11 |   15  Charlie | 12 |   16  Lucy | 2 |   17  Charlie | 10 |   18  Lily  | 5 |   19  Ytt  | 15 |   20 (20 rows)

以上为个人经验，希望能给大家一个参考，也希望大家多多支持。如有错误或未考虑完全的地方，望不吝赐教。

原文链接：http://www.linuxidc.com/Linux/2015-04/116349.htm