平时在客户业务的数据分析中,经常有这么一种需求,那就是希望得到某个组里面某些字段最大或最小的记录. 比如雇员表: ``` [local]:5432 pg12@testdb=# \d employee Table "public.employee" Column | Type | Collation | Nullable | Default ------------+-----------------------+-----------+----------+--------- id | integer | | | name | character varying(30) | | | department | character varying(30) | | | salary | double precision | ``` 我们通过[MockData](https://www.mockaroo.com/)生成测试数据,共1000行,department共有12个. ``` [local]:5432 pg12@testdb=# select count(*) from employee; count ------- 1000 (1 row) Time: 22.747 ms [local]:5432 pg12@testdb=# select distinct department from employee; department -------------------------- Marketing Training Sales Business Development Product Management Research and Development Support Legal Accounting Services Human Resources Engineering (12 rows) Time: 2.616 ms ``` 下面希望得到每个department中salary中最大的employee. 常规的做法是使用分组求得最大值/最小值,然后进行关联查询: ``` [local]:5432 pg12@testdb=# select a.* from employee a,(select department,max(salary) as salary from employee group by department) b pg12@testdb-# where a.department = b.department and a.salary = b.salary order by a.department; id | name | department | salary -----+--------------------+--------------------------+--------- 453 | Ericha Hendrikse | Accounting | 9958.5 307 | Kyle Hartegan | Business Development | 9754.93 969 | Odelinda Marsden | Engineering | 9942.3 201 | Glen Kasperski | Human Resources | 9559.54 892 | Mirabelle Lesslie | Legal | 9720.49 214 | Chane Koschek | Marketing | 9943.86 371 | Josy Ayliff | Product Management | 9975.48 191 | Meir Alvaro | Research and Development | 9870 770 | Adoree de Guerre | Sales | 9808.65 370 | Benoite Overlow | Services | 9884.79 866 | Shirlee McIlherran | Support | 9884.08 586 | Renae Jerromes | Training | 9904.24 (12 rows) Time: 8.256 ms [local]:5432 pg12@testdb=# ``` 这种方法有个问题是如果max salary有多条记录的话,上述查询的结果会有多条. PostgreSQL提供了DISTINCT ON,可简单实现该需求 ``` [local]:5432 pg12@testdb=# SELECT DISTINCT ON (department) pg12@testdb-# * pg12@testdb-# FROM pg12@testdb-# employee pg12@testdb-# ORDER BY pg12@testdb-# department, pg12@testdb-# salary DESC; id | name | department | salary -----+--------------------+--------------------------+--------- 453 | Ericha Hendrikse | Accounting | 9958.5 307 | Kyle Hartegan | Business Development | 9754.93 969 | Odelinda Marsden | Engineering | 9942.3 201 | Glen Kasperski | Human Resources | 9559.54 892 | Mirabelle Lesslie | Legal | 9720.49 214 | Chane Koschek | Marketing | 9943.86 371 | Josy Ayliff | Product Management | 9975.48 191 | Meir Alvaro | Research and Development | 9870 770 | Adoree de Guerre | Sales | 9808.65 370 | Benoite Overlow | Services | 9884.79 866 | Shirlee McIlherran | Support | 9884.08 586 | Renae Jerromes | Training | 9904.24 (12 rows) Time: 11.445 ms ``` Excellent Feature! **参考资料** [The Many Faces of DISTINCT in PostgreSQL](https://hakibenita.com/the-many-faces-of-distinct-in-postgre-sql)