专栏首页Ryan Miaopostgresql中使用distinct去重

postgresql中使用distinct去重

select语法

[ WITH [ RECURSIVE ] with_query [, ...] ]
SELECT [ ALL | DISTINCT [ ON ( expression [, ...] ) ] ]
    [ * | expression [ [ AS ] output_name ] [, ...] ]
    [ FROM from_item [, ...] ]
    [ WHERE condition ]
    [ GROUP BY grouping_element [, ...] ]
    [ HAVING condition [, ...] ]
    [ WINDOW window_name AS ( window_definition ) [, ...] ]
    [ { UNION | INTERSECT | EXCEPT } [ ALL | DISTINCT ] select ]
    [ ORDER BY expression [ ASC | DESC | USING operator ] [ NULLS { FIRST | LAST } ] [, ...] ]
    [ LIMIT { count | ALL } ]
    [ OFFSET start [ ROW | ROWS ] ]
    [ FETCH { FIRST | NEXT } [ count ] { ROW | ROWS } ONLY ]
    [ FOR { UPDATE | NO KEY UPDATE | SHARE | KEY SHARE } [ OF table_name [, ...] ] [ NOWAIT | SKIP LOCKED ] [...] ]

where from_item can be one of:

    [ ONLY ] table_name [ * ] [ [ AS ] alias [ ( column_alias [, ...] ) ] ]
                [ TABLESAMPLE sampling_method ( argument [, ...] ) [ REPEATABLE ( seed ) ] ]
    [ LATERAL ] ( select ) [ AS ] alias [ ( column_alias [, ...] ) ]
    with_query_name [ [ AS ] alias [ ( column_alias [, ...] ) ] ]
    [ LATERAL ] function_name ( [ argument [, ...] ] )
                [ WITH ORDINALITY ] [ [ AS ] alias [ ( column_alias [, ...] ) ] ]
    [ LATERAL ] function_name ( [ argument [, ...] ] ) [ AS ] alias ( column_definition [, ...] )
    [ LATERAL ] function_name ( [ argument [, ...] ] ) AS ( column_definition [, ...] )
    [ LATERAL ] ROWS FROM( function_name ( [ argument [, ...] ] ) [ AS ( column_definition [, ...] ) ] [, ...] )
                [ WITH ORDINALITY ] [ [ AS ] alias [ ( column_alias [, ...] ) ] ]
    from_item [ NATURAL ] join_type from_item [ ON join_condition | USING ( join_column [, ...] ) ]

and grouping_element can be one of:

    ( )
    expression
    ( expression [, ...] )
    ROLLUP ( { expression | ( expression [, ...] ) } [, ...] )
    CUBE ( { expression | ( expression [, ...] ) } [, ...] )
    GROUPING SETS ( grouping_element [, ...] )

and with_query is:

    with_query_name [ ( column_name [, ...] ) ] AS ( select | values | insert | update | delete )

TABLE [ ONLY ] table_name [ * ]

数据

INSERT INTO "test_dist" VALUES (1, '1', 'a');
INSERT INTO "test_dist" VALUES (2, '1', 'b');
INSERT INTO "test_dist" VALUES (3, '1', 'c');
INSERT INTO "test_dist" VALUES (4, '2', 'm');
INSERT INTO "test_dist" VALUES (5, '2', 'n');
INSERT INTO "test_dist" VALUES (6, '3', 'j');
INSERT INTO "test_dist" VALUES (7, '3', 'j');
INSERT INTO "test_dist" VALUES (8, '4', 'j');

去重多个列

直接用distinct,后面的列都参与去重。只有code, name拼接的组合相同时,去掉重复的

# SELECT 
DISTINCT code,name
from test_dist;
 code | name
------+------
 1    | b
 2    | n
 4    | j
 1    | c
 1    | a
 2    | m
 3    | j
(7 rows)

去重指定列,保留其他列

当下遇到需求,需要将其中一个列去重,然后其他列随机取出就可以了。造成这种需求的原因是单表设计不合理,没有拆分成多表,造成多字段冗余,除了唯一性标志外,其他字段是相同的。目标是,取出其他字段,忽略唯一标志。

因为其他字段有重复,需要去掉重复。

# SELECT 
DISTINCT ON (code) code,
id, name
from test_dist;
 code | id | name
------+----+------
 1    |  1 | a
 2    |  4 | m
 3    |  6 | j
 4    |  8 | j
(4 rows)

这里,根据code去重,id和name随机取出,这样可以获得code维度的数据。如果不去重,获得原始数据,code有重复。

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

我来说两句

0 条评论
登录 后参与评论

相关文章

  • Postgresql插入或更新操作upsert

    幂等性的一个要求是多次操作的结果一致。对于update操作,多次直接的结果都是最后update的值,是满足需求的。但对于insert,如果已经插入,第二次会报错...

    Ryan-Miao
  • sql格式化并高亮

    演示地址: https://ryan-miao.github.io/sql-format-with-highlight/index.html

    Ryan-Miao
  • Nginx代理proxy pass配置去除前缀

    比如,访问abc.com/appv2/a/b.html, 要求转发到localhost:8088/appv2/a/b.html

    Ryan-Miao
  • python目录操作一

    Python os 模块提供了一个统一的操作系统接口函数, 这些接口函数通常是平 台指定的,os 模块能在不同操作系统平台(如 nt 或 posix)中的特定函...

    py3study
  • python【第五篇】常用模块学习

    a.定义:本质就是.py结尾的python文件,逻辑上组织python代码,实现某种功能。例:文件名test.py-->模块名test。

    用户1432189
  • python批量新建文件夹

    因为要经常写课备课,年级较多(G5-G11),每个年级的课程也比较多(1-100),每次都要新建文件夹,觉的比较麻烦,于是想着去写一个简单小程序去批量自动创建文...

    用户7054460
  • python ping 判断主机是否连通

    py3study
  • Python 技术篇-读取遍历指定路径的文件,区分文件和文件夹

    上面的方法是展示所有的文件和文件夹,接下来我们来改进一下,区分文件和文件夹,并分别展示出来

    小蓝枣
  • 谷歌全新轻量级新模型ALBERT刷新三大NLP基准!

    由图灵奖获得者、人工智能巨头Yoshua Bengio 和 Yann LeCun牵头创办的顶级会议ICLR,被誉为深度学习“无冕之王”,获得学术研究者们广泛认可...

    新智元
  • 关于python中的IO流

    只要有IO,那么就会发生IOError。所以尽量每次都要使用try...finally

    py3study

扫码关注云+社区

领取腾讯云代金券