PostgreSQL distinct 和 distinct on 区别

数据

city空值的为null

z-blog=# select city, id, ip from ip_pool;
  city  | id |       ip
--------+----+----------------
 张家口 |  2 | 47.92.133.31
 张家口 |  4 | 47.92.33.91
        |  5 | 106.38.241.179
        |  6 | 222.64.157.100
        |  7 | 47.101.204.90
        |  8 | 203.208.60.75
        |  9 | 203.208.60.8
(7 行记录)

distinct

按city，id，ip三个字段，组合唯一

z-blog=# select distinct city, id, ip from ip_pool;
  city  | id |       ip
--------+----+----------------
 张家口 |  2 | 47.92.133.31
 张家口 |  4 | 47.92.33.91
        |  5 | 106.38.241.179
        |  6 | 222.64.157.100
        |  7 | 47.101.204.90
        |  8 | 203.208.60.75
        |  9 | 203.208.60.8
(7 行记录)

distinct可用all代替。

z-blog=# select all city, id, ip from ip_pool;
  city  | id |       ip
--------+----+----------------
 张家口 |  2 | 47.92.133.31
 张家口 |  4 | 47.92.33.91
        |  5 | 106.38.241.179
        |  6 | 222.64.157.100
        |  7 | 47.101.204.90
        |  8 | 203.208.60.75
        |  9 | 203.208.60.8
(7 行记录)

distinct on

按city为唯一

z-blog=# select distinct on(city) city, id, ip from ip_pool;
  city  | id |       ip
--------+----+----------------
 张家口 |  2 | 47.92.133.31
        |  5 | 106.38.241.179
(2 行记录)