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有重复。