PostgreSQL distinct 和 distinct on 区别

PostgreSQL About 1,179 words

数据

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 行记录)
Views: 2,267 · Posted: 2021-01-05

————        END        ————

Give me a Star, Thanks:)

https://github.com/fendoudebb/LiteNote

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

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


Today On History
Browsing Refresh