用法：
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/
