Mysql 简明教程

MySQL - Distinct Clause

MySQL DISTINCT clause

MySQL 中的 DISTINCT 从一个表或多个表中返回不同值（唯一值）以与 SELECT 语句配合使用。它忽略特定列中存在的所有重复值，仅返回不同值。

The DISTINCT clause in MySQL is used with a SELECT statement to return the distinct values (unique values) from a single or multiple of columns in a table. It ignores all the duplicates values present in the particular column(s) and returns only the distinct values.

此子句可用于多种场景，例如识别唯一客户姓名、唯一客户 ID 等。可与 WHERE、ORDER BY 和 GROUP BY 等其他子句结合使用以进一步筛选数据。

We can use this clause in various scenarios, such as identifying unique customer names, unique customer id’s, etc. It can be combined with other clauses such as WHERE, ORDER BY, and GROUP BY to filter the data further.

Syntax

以下是 MySQL 中 DISTINCT 子句的语法：

Following is the syntax of the DISTINCT clause in MySQL −

SELECT DISTINCT column1, column2, ..., columnN
FROM table_name
WHERE conditions // optional

其中，

Where,

  1. (column1, column2,…​,columnN) are the columns from which we want the distinct (unique) values.

  2. table_name is the name of the table from which we want to select data.

  3. WHERE conditions is optional. These are used to filter the data.

Example

首先，让我们创建一个名为 CUSTOMERS 的表使用以下 INSERT 查询：

Firstly, let us create a create a table named CUSTOMERS using the following INSERT query −

CREATE TABLE CUSTOMERS (
   ID INT NOT NULL,
   NAME VARCHAR (20) NOT NULL,
   AGE INT NOT NULL,
   ADDRESS CHAR (25),
   SALARY DECIMAL (18, 2),
   PRIMARY KEY (ID)
);

以下 INSERT INTO 语句将 7 条记录添加到上面创建的表中：

The following INSERT INTO statement adds 7 records into the above-created table −

INSERT INTO CUSTOMERS(ID, NAME, AGE, ADDRESS, SALARY) VALUES
(1, 'Ramesh', 32, 'Hyderabad', NULL),
(2, 'Khilan', 25, 'Delhi', 1500.00),
(3, 'Kaushik', 23, 'Hyderabad', 2000.00),
(4, 'Chaital', 25, 'Mumbai', NULL),
(5, 'Hardik', 27, 'Vishakapatnam', 8500.00),
(6, 'Komal', 22, 'Vishakapatnam', 4500.00),
(7, 'Muffy', 24, 'Indore', 10000.00);

执行以下查询以显示 CUSTOMERS 表中插入的所有记录：

Execute the below query to display all the inserted records in the CUSTOMERS table −

SELECT * FROM CUSTOMERS;

以下为 CUSTOMERS 表 −

Following is the CUSTOMERS table −

现在，让我们检索 CUSTOMERS 表中的 ADDRESS 列，不用 DISTINCT 子句。

Now, let us retrieve the ADDRESS column from CUSTOMERS table without using the DISTINCT clause.

SELECT ADDRESS FROM CUSTOMERS;

ADDRESS 列中不忽略重复值。

Duplicate values are not ignored in the ADDRESS column.

在此，我们在 ADDRESS 列中使用 DISTINCT 子句

Here, we are using the DISTINCT clause on the ADDRESS column −

SELECT DISTINCT ADDRESS FROM CUSTOMERS;

Output

正如我们从下面的输出中看到的，ADDRESS 列中的重复值将被忽略。

As we can see in the output below, duplicate values are ignored in the ADDRESS column.

DISTINCT Clause with COUNT() Function

MySQL count() 函数允许我们计算表的一列或多列中存在的唯一值的数量。让我们了解以下示例：

The MySQL count() function allows us to count the number of distinct values present in one or more columns of a table. Let us understand with the example below

Example

在以下查询中，我们正在使用 MySQL COUNT() 函数来计算 CUSTOMERS 表中 ADDRESS 列的 DISTINCT 记录：

In the following query, we are using the MySQL COUNT() function to count the DISTINCT records in ADDRESS column of CUSTOMERS table −

SELECT COUNT(DISTINCT ADDRESS) FROM CUSTOMERS;

Output

ADDRESS 列中存在 5 个不同的记录。

There are 5 distinct records present in the ADDRESS column.

Example

在此查询中，我们正在从 ADDRESS 为“Hyderabad”的 CUSTOMERS 表中检索唯一的 SALARY 记录。

In this query, we are retrieving unique SALARY records from the CUSTOMERS table where the ADDRESS is "Hyderabad".

SELECT DISTINCT SALARY FROM CUSTOMERS WHERE ADDRESS = "HYDERABAD";

Output

以上程序的输出如下所示：

The output for the program above is produced as given below −

DISTINCT on Multiple Columns

我们可以在表的多个列上使用 MySQL DISTINCT 关键字来返回这些列中所有唯一的值组合，即移除表中的冗余记录。

We can use the MySQL DISTINCT keyword on multiple columns of a table to return all the unique combinations of values across those columns, i.e. removing redundant records in a table.

Example

在以下查询中，我们正在从 CUSTOMERS 表中检索 ADDRESS 和 SALARY 列的不同组合，并按 ADDRESS 列升序对结果集进行排序。

In the following query, we are retrieving the distinct combinations of ADDRESS and SALARY columns from the CUSTOMERS table and orders the result set by the ADDRESS column in ascending order.

SELECT DISTINCT ADDRESS, SALARY FROM CUSTOMERS ORDER BY ADDRESS;

Output

正如我们从下面的输出中看到的，重复值“Hyderabad”和“Vishakapatnam”在结果集中出现了两次，这是因为 Hyderabad 和 Vishakapatnam 与 SALARY 的每个组合都是唯一的。

As we can see in the output below, the duplicate values "Hyderabad" and "Vishakapatnam" appears twice in the result set because each combination of Hyderabad and Vishakapatnam with SALARY is unique.

DISTINCT with NULL values

如果特定列中存在 NULL 值，MySQL DISTINCT 将把它们当作唯一值并包含在结果集中。

If there are NULL values present in a specific column, the MySQL DISTINCT will treat them as unique values and includes them in the result set.

Example

此处，我们正在使用以下查询返回客户的唯一薪水：

Here, we are returning the distinct salary of the customers using the following query −

SELECT DISTINCT SALARY FROM CUSTOMERS ORDER BY SALARY;

Output

执行给定的程序后，输出如下所示：

On executing the given program, the output is displayed as follows −

Distinct Clause Using a Client Program

除了使用 MySQL 查询从表中提取不同的记录外，你还可以使用客户端程序执行 DISTINCT 操作。

In addition to fetch distinct records from a table with a MySQL query, you can also use a client program to perform the DISTINCT operation.

Syntax

以下是此操作在各种编程语言中的语法 −

Following are the syntaxes of this operation in various programming languages −

Example

以下是这些程序 −

Following are the programs −