postgresql中使用distinct去重

时间:2021-03-29 14:22:08

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