Manon.icu

I'm here to make you a better developer by teaching you everything I know about building for the web.

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 ;

M08Pm2

单列示例

SELECT
	DISTINCT bcolor
FROM
	distinct_demo
ORDER BY
	bcolor;

1RIClu

多列示例

SELECT
	DISTINCT bcolor,
	fcolor
FROM
	distinct_demo
ORDER BY
	bcolor,
	fcolor;

kxyiii

多列示例中,同时指定了bgcolorfcolor,Postgresql 结合列的值计算并返回每行的唯一性。

DISTINCT ON (expression)示例

SELECT
	DISTINCT ON (bcolor) bcolor,
	fcolor
FROM
	distinct_demo
ORDER BY
	bcolor,
	fcolor;

以上语句按照bgcolorfcolor进行排序,对于每组重复项,保留并返回第一行。

XwiCGF