使用Postgres从每个组中获取一个对象

Question

我正在处理一张猫的表格，并试图写一条语句，从每个年龄组中选择一只猫。

这是我的桌子

pets=# SELECT * FROM cats;
 id |   name   | gender | age |     intake_date     |    adoption_date    
----+----------+--------+-----+---------------------+---------------------
  1 | Mushi    | M      |   1 | 2016-01-09 00:00:00 | 2016-03-22 00:00:00
  2 | Seashell | F      |   7 | 2016-01-09 00:00:00 | 
  3 | Azul     | M      |   3 | 2016-01-11 00:00:00 | 2016-04-17 00:00:00
  4 | Victoire | M      |   7 | 2016-01-11 00:00:00 | 2016-09-01 00:00:00
  5 | Nala     | F      |   1 | 2016-01-12 00:00:00 | 
(5 rows)

Answer 1

使用distinct on

select distinct on (c.age) c.*
from cats c
order by age;