PostgreSQL distinct和distinct on区别

PostgreSQL大约 1178 字

数据

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

cityidip三个字段,组合唯一

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 行记录)
阅读 35 · 发布于 2021-01-05

————        END        ————

扫描下方二维码关注公众号和小程序↓↓↓

昵称:
随便看看换一批