Sql 简明教程
SQL - DISTINCT Keyword
The SQL DISTINCT Keyword
SQL DISTINCT 关键字与 SELECT 语句一起使用，用于从表中获取唯一记录。
The SQL DISTINCT keyword is used in conjunction with the SELECT statement to fetch unique records from a table.
当需要避免任何特定列/表中现有的重复值时，我们对 SELECT 语句使用 DISTINCT 关键字。当我们使用 DISTINCT 关键字时，SELECT 语句只返回表中可用的唯一记录。
We use DISTINCT keyword with the SELECT statetment when there is a need to avoid duplicate values present in any specific columns/tables. When we use DISTINCT keyword, SELECT statement returns only the unique records available in the table.
Syntax
SQL DISTINCT 关键字的基本语法如下 −
The basic syntax of SQL DISTINCT keyword is as follows −
SELECT DISTINCT column1, column2,.....columnN
FROM table_name;其中 column1, column2, 等是我们希望从中获取唯一或不同值的目标列； table_name 表示包含数据的表的名称。
Where, column1, column2, etc. are the columns we want to retrieve the unique or distinct values from; and table_name represents the name of the table containing the data.
DISTINCT Keyword on Single Columns
我们可以对一列使用 DISTINCT 关键字来获取该列中的所有唯一值，即删除重复项。此方法通常用于获取特定列中唯一值汇总或者消除冗余数据。
We can use the DISTINCT keyword on a single column to retrieve all unique values in that column, i.e. with duplicates removed. This is often used to get a summary of the distinct values in a particular column or to eliminate redundant data.
Example
假设我们已使用 CREATE TABLE 语句在 MySQL 数据库中创建了一个名为 CUSTOMERS 的表，如下所示 −
Assume we have created a table with name CUSTOMERS in MySQL database using CREATE TABLE statement as shown below −
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 语句将值插入此表：
Following query inserts values into this table using the INSERT statement −
INSERT INTO CUSTOMERS VALUES
(1, 'Ramesh', 32, 'Ahmedabad', 2000.00),
(2, 'Khilan', 25, 'Delhi', 1500.00),
(3, 'Kaushik', 23, 'Kota', 2000.00),
(4, 'Chaitali', 25, 'Mumbai', 6500.00),
(5, 'Hardik', 27, 'Bhopal', 8500.00),
(6, 'Komal', 22, 'Hyderabad', 4500.00),
(7, 'Muffy', 24, 'Indore', 10000.00);获得的表如下所示：
The table obtained is as shown below −
ID |
NAME |
AGE |
ADDRESS |
SALARY |
1 |
Ramesh |
32 |
Ahmedabad |
2000.00 |
2 |
Khilan |
25 |
Delhi |
1500.00 |
3 |
Kaushik |
23 |
Kota |
2000.00 |
4 |
Chaitali |
25 |
Mumbai |
6500.00 |
5 |
Hardik |
27 |
Bhopal |
8500.00 |
6 |
Komal |
22 |
Hyderabad |
4500.00 |
7 |
Muffy |
24 |
Indore |
10000.00 |
首先，让我们使用 SELECT 查询从 CUSTOMERS 表中获取 SALARY 值 −
First, let us retrieve the SALARY values from the CUSTOMERS table using the SELECT query −
SELECT SALARY FROM CUSTOMERS ORDER BY SALARY;这将产生以下结果。在这里，您可以看到 SALARY 值 2000 出现了两次 −
This would produce the following result. Here, you can observe that the salary value 2000 is appearing twice −
SALARY |
1500.00 |
2000.00 |
2000.00 |
4500.00 |
6500.00 |
8500.00 |
10000.00 |
现在，我们对上面的 SELECT 查询使用 DISTINCT 关键字，然后查看结果 −
Now, let us use the DISTINCT keyword with the above SELECT query and then see the result −
SELECT DISTINCT SALARY FROM CUSTOMERS ORDER BY SALARY;DISTINCT Keyword on Multiple Columns
我们还可以对多列使用 DISTINCT 关键字，以获取跨这些列的所有唯一值的组合。此方法通常用于获取多列中唯一值汇总，或者消除冗余数据。
We can also use the DISTINCT keyword on multiple columns to retrieve all unique combinations of values across those columns. This is often used to get a summary of distinct values in multiple columns, or to eliminate redundant data.
Example
在以下查询中，我们使用 DISTINCT 关键字获取客户年龄和工资的所有唯一值组合 −
In the following query, we are retrieving a list of all unique combinations of customer’s age and salary using the DISTINCT keyword −
SELECT DISTINCT AGE, SALARY FROM CUSTOMERS ORDER BY AGE;Output
虽然 AGE 列在两个记录中具有值“25”，但“25”的每一个特定“工资”组合都是唯一的，因此两个行都包含在结果集中 −
Though the AGE column have the value "25" in two records, each combination of "25" with it’s specific 'salary' is unique, so both rows are included in the result set −
AGE |
SALARY |
22 |
4500.00 |
23 |
2000.00 |
24 |
10000.00 |
25 |
1500.00 |
25 |
6500.00 |
27 |
8500.00 |
32 |
2000.00 |
DISTINCT Keyword with COUNT() Function
COUNT() 函数用于获取 SELECT 查询返回的记录数。我们需要将一个表达式传递给此函数，以便 SELECT 查询返回满足指定表达式条件的记录数。
The COUNT() function is used to get the number of records retuned by the SELECT query. We need to pass an expression to this function so that the SELECT query returns the number of records that satisfy the specified expression.
如果我们将 DISTINCT 关键字作为表达式传递给 COUNT() 函数，它将返回表中某一列中的唯一值数。
If we pass the DISTINCT keyword to the COUNT() function as an expression, it returns the number of unique values in a column of a table.
Syntax
以下是在 COUNT() 函数中使用 DISTINCT 关键字的语法 −
Following is the syntax for using the DISTINCT keyword with COUNT() function −
SELECT COUNT(DISTINCT column_name)
FROM table_name WHERE condition;其中， column_name 是要为其统计唯一值的列的名称； table_name 是包含此数据的表的名称。
Where, column_name is the name of the column for which we want to count the unique values; and table_name is the name of the table that contains the data.
DISTINCT Keyword with NULL Values
在 SQL 中，当列中有 NULL 值时，DISTINCT 会将它们视为唯一值，并将它们包括在结果集中。
In SQL, when there are NULL values in the column, DISTINCT treats them as unique values and includes them in the result set.
Example
首先，请允许我们更新 CUSTOMERS 表的两个记录，并将它们的工资值修改为 NULL
First of all let us update two records of the CUSTOMERS table and modify their salary values to NULL
UPDATE CUSTOMERS SET SALARY = NULL WHERE ID IN(6,4);得到的 CUSTOMERS 表如下 −
The resultant CUSTOMERS table would be −
ID |
NAME |
AGE |
ADDRESS |
SALARY |
1 |
Ramesh |
32 |
Ahmedabad |
2000.00 |
2 |
Khilan |
25 |
Delhi |
1500.00 |
3 |
Kaushik |
23 |
Kota |
2000.00 |
4 |
Chaitali |
25 |
Mumbai |
NULL |
5 |
Hardik |
27 |
Bhopal |
8500.00 |
6 |
Komal |
22 |
Hyderabad |
NULL |
7 |
Muffy |
24 |
Indore |
10000.00 |
现在，我们使用以下查询检索客户的不同工资 −
Now, we are retrieving the distinct salary of the customers using the following query −
SELECT DISTINCT SALARY FROM CUSTOMERS ORDER BY SALARY;