Published 2022-06-02
Postgresql - Select Distinct
DISTINCT
用于返回数据删除重复行
语法
#单列
SELECT
DISTINCT column1
FROM
table_name;
#多列
SELECT
DISTINCT column1, column2
FROM
table_name;
DISTINCT ON (expression)
,保留每组重复性的第一行。
SELECT
DISTINCT ON (column1) column_alias,
column2
FROM
table_name
ORDER BY
column1,
column2;
DISTINCT ON (expression)
必需是ORDER BY
最左边的表达式
示例
创建名为distinct_demo
的新表,导入数据:
#创建新表
CREATE TABLE distinct_demo (
id serial NOT NULL PRIMARY KEY,
bcolor VARCHAR,
fcolor VARCHAR
);
#插入数据
INSERT INTO distinct_demo (bcolor, fcolor)
VALUES
('red', 'red'),
('red', 'red'),
('red', NULL),
(NULL, 'red'),
('red', 'green'),
('red', 'blue'),
('green', 'red'),
('green', 'blue'),
('green', 'green'),
('blue', 'red'),
('blue', 'green'),
('blue', 'blue');
#查询数据
SELECT
id,
bcolor,
fcolor
FROM
distinct_demo ;
单列示例
SELECT
DISTINCT bcolor
FROM
distinct_demo
ORDER BY
bcolor;
多列示例
SELECT
DISTINCT bcolor,
fcolor
FROM
distinct_demo
ORDER BY
bcolor,
fcolor;
多列示例中,同时指定了bgcolor
和fcolor
,Postgresql 结合列的值计算并返回每行的唯一性。
DISTINCT ON (expression)
示例
SELECT
DISTINCT ON (bcolor) bcolor,
fcolor
FROM
distinct_demo
ORDER BY
bcolor,
fcolor;
以上语句按照bgcolor
和fcolor
进行排序,对于每组重复项,保留并返回第一行。