Postgresql 简明教程

PostgreSQL - DISTINCT Keyword

PostgreSQL DISTINCT 关键字与 SELECT 语句结合使用，以消除所有重复记录并仅获取唯一记录。

The PostgreSQL DISTINCT keyword is used in conjunction with SELECT statement to eliminate all the duplicate records and fetching only unique records.

在表中，你可能会遇到重复记录的情况。在获取这些记录时，只获取唯一记录比获取重复记录更有意义。

There may be a situation when you have multiple duplicate records in a table. While fetching such records, it makes more sense to fetch only unique records instead of fetching duplicate records.

Syntax

消除重复记录的 DISTINCT 关键字的基本语法如下 -

The basic syntax of DISTINCT keyword to eliminate duplicate records is as follows −

SELECT DISTINCT column1, column2,.....columnN
FROM table_name
WHERE [condition]

Example

考虑 COMPANY 表具有以下记录：

Consider the table COMPANY having records as follows −

# select * from COMPANY;
 id | name  | age | address   | salary
----+-------+-----+-----------+--------
  1 | Paul  |  32 | California|  20000
  2 | Allen |  25 | Texas     |  15000
  3 | Teddy |  23 | Norway    |  20000
  4 | Mark  |  25 | Rich-Mond |  65000
  5 | David |  27 | Texas     |  85000
  6 | Kim   |  22 | South-Hall|  45000
  7 | James |  24 | Houston   |  10000
(7 rows)

现在，让我们向该表格添加以下两条记录 -

Let us add two more records to this table as follows −

INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (8, 'Paul', 32, 'California', 20000.00 );

INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (9, 'Allen', 25, 'Texas', 15000.00 );

现在，COMPANY 表中的记录将为 -

Now, the records in the COMPANY table would be −

 id | name  | age | address    | salary
----+-------+-----+------------+--------
  1 | Paul  |  32 | California |  20000
  2 | Allen |  25 | Texas      |  15000
  3 | Teddy |  23 | Norway     |  20000
  4 | Mark  |  25 | Rich-Mond  |  65000
  5 | David |  27 | Texas      |  85000
  6 | Kim   |  22 | South-Hall |  45000
  7 | James |  24 | Houston    |  10000
  8 | Paul  |  32 | California |  20000
  9 | Allen |  25 | Texas      |  15000
(9 rows)

首先，让我们看看以下 SELECT 查询如何返回重复工资记录 -

First, let us see how the following SELECT query returns duplicate salary records −

testdb=# SELECT name FROM COMPANY;

这将产生以下结果 -

This would produce the following result −

 name
-------
 Paul
 Allen
 Teddy
 Mark
 David
 Kim
 James
 Paul
 Allen
(9 rows)

现在，让我们将 DISTINCT 关键字与上面的 SELECT 查询一起使用，看看结果 -

Now, let us use DISTINCT keyword with the above SELECT query and see the result −

testdb=# SELECT DISTINCT name FROM COMPANY;

这将产生以下结果，其中没有任何重复的条目 −

This would produce the following result where we do not have any duplicate entry −

 name
-------
 Teddy
 Paul
 Mark
 David
 Allen
 Kim
 James
(7 rows)