PostgreSQL DISTINCT ON

概述用法： 　　DISTINCT ON ( expression [, …] ) keeps only the first row of each set of rows where the given expressions evaluate to equal. […]。 Note that the “first row” of each set is unpredictable unless OR

用法：
　　disTINCT ON ( Expression [,…] ) keeps only the first row of each set of rows where the given Expressions evaluate to equal. […]。 Note that the “first row” of each set is unpredictable unless ORDER BY is used to ensure that the desired row appears first. […]。 The disTINCT ON Expression(s) must match the leftmost ORDER BY Expression(s)。
　　意思是disTINCT ON ( Expression [,…] )把记录根据[,…]的值进行分组，分组之后仅返回每一组的第一行。需要注意的是，如果你不指定ORDER BY子句，返回的第一条的不确定的。如果你使用了ORDER BY 子句，那么[,…]里面的值必须靠近ORDER BY子句的最左边。

例子测试数据参考http://www.jb51.cc/article/p-mygxkbch-zc.html。

1. 当没用指定ORDER BY子句的时候返回的记录是不确定的。

postgres=# select distinct on(course)ID,name,course,score from student; ID | name | course | score ----+--------+--------+------- 10 | 周星驰 | 化学 | 83 8 | 周星驰 | 外语 | 88 2 | 周润发 | 数学 | 99 14 | 黎明 | 物理 | 90 6 | 周星驰 | 语文 | 91(5 rows)

2. 获取每门课程的最高分

postgres=# select distinct on(course)ID,score from student order by course,score desc; ID | name | course | score ----+--------+--------+------- 5 | 周润发 | 化学 | 87 13 | 黎明 | 外语 | 95 2 | 周润发 | 数学 | 99 14 | 黎明 | 物理 | 90 6 | 周星驰 | 语文 | 91(5 rows)

3. 如果指定ORDER BY 必须把分组的字段放在最左边

postgres=# select distinct on(course)ID,score from student order by score desc;ERROR:  SELECT disTINCT ON Expressions must match initial ORDER BY Expressions liNE 1: select distinct on(course)ID,score from student ... 

postgres=# select distinct on(course)ID,score from student order by score desc,course;ERROR:  SELECT disTINCT ON Expressions must match initial ORDER BY Expressions liNE 1: select distinct on(course)ID,score from student ...

4. 获取每门课程的最高分同样可以使用IN子句来实现

postgres=# select * from student where(course,score) in(select course,max(score) from student group by course); ID |  name  | course | score ----+--------+--------+-------  2 | 周润发 | 数学   |    99  5 | 周润发 | 化学   |    87  6 | 周星驰 | 语文   |    91 13 | 黎明   | 外语   |    95 14 | 黎明   | 物理   |    90(5 rows)

5. 在 row_number() over(),distinct on和in子句之间有一个小区别
　　比如当数学的最高分同时有两个人时候，row_number() over(),distinct on经过排序后是返回这两个人中的随机一个人，除非你根据人名在进行排序，而in子句会全部返回

先将数学的最高分更新有两个人

postgres=# update student set score =99 where name='黎明' and course='数学';UPDATE 1 postgres=# select * from student where course ='数学'; ID |  name  | course | score ----+--------+--------+------- 31 | 周润发 | 数学   |    99 36 | 周星驰 | 数学   |    81 41 | 黎明   | 数学   |    99(3 rows)

获取每门课程的最高分的人
使用 rom_number() over()

postgres=# select ID,score from (select *,row_number() over(partition by course order by score desc)rn from student)t where t.rn=1 order by course; ID |  name  | course | score ----+--------+--------+------- 34 | 周润发 | 化学   |    87 42 | 黎明   | 外语   |    95 41 | 黎明   | 数学   |    99 43 | 黎明   | 物理   |    90 35 | 周星驰 | 语文   |    91(5 rows)

使用 distinct on

postgres=# select distinct on(course) * from student order by course,score desc,course; ID | name | course | score ----+--------+--------+------- 34 | 周润发 | 化学 | 87 42 | 黎明 | 外语 | 95 41 | 黎明 | 数学 | 99 43 | 黎明 | 物理 | 90 35 | 周星驰 | 语文 | 91(5 rows)

使用 in子句

postgres=# select * from student where(course,score) in (select course,max(score) from student group by course) order by course; ID |  name  | course | score ----+--------+--------+------- 34 | 周润发 | 化学   |    87 42 | 黎明   | 外语   |    95 31 | 周润发 | 数学   |    99 41 | 黎明   | 数学   |    99 43 | 黎明   | 物理   |    90 35 | 周星驰 | 语文   |    91(6 rows)

　　因为row_number() over()是根据行号来取的，distinct on是返回排序之后的第一行，所以它们只是返回一个最高分。

　　为了解决rom_number() over()和distinct on的问题，可以使用rank() over()窗口函数，rank() 窗口函数和 row_number() 窗口函数类似，但 rank() 窗口函数会将结果集分组后相同值的记录的标记相等。

postgres=# select ID,score from(select *,rank() over(partition by course order by score desc)sn from student)t where sn=1; ID |  name  | course | score ----+--------+--------+-------  5 | 周润发 | 化学   |    87 13 | 黎明   | 外语   |    95 12 | 黎明   | 数学   |    99  2 | 周润发 | 数学   |    99 14 | 黎明   | 物理   |    90  6 | 周星驰 | 语文   |    91(6 rows)

参考：
http://stackoverflow.com/questions/9795660/postgresql-distinct-on-with-different-order-by
http://blog.163.com/digoal@126/blog/static/16387704020124239390354/
http://francs3.blog.163.com/blog/static/4057672720126209947340/

总结

以上是内存溢出为你收集整理的PostgreSQL DISTINCT ON全部内容，希望文章能够帮你解决PostgreSQL DISTINCT ON所遇到的程序开发问题。

如果觉得内存溢出网站内容还不错，欢迎将内存溢出网站推荐给程序员好友。