Skip to main content

使用MySQL DISTINCT消除重复

不同从句导论

从表中查询数据时,可能会得到重复的行。要删除这些重复行,可以在SELECT语句中使用DISTINCT子句。

以下是DISTINCT子句的语法:

SELECT DISTINCT
select_list
FROM
table_name
WHERE
search_condition
ORDER BY
sort_expression;

在这种语法中,可以在select distinct关键字之后指定一个或多个要选择不同值的列。

如果指定一列,DISTINCT子句将根据该列的值计算行的唯一性。

但是,如果指定两个或更多列,DISTINCT子句将使用这些列的值来评估行的唯一性。

当使用DISTINCT子句执行SELECT语句时,MySQL会在FROM、WHERE和SELECT子句之后、ORDER BY子句之前计算DISTINCT子句:

MySQL独立子句示例

我们将使用示例数据库中的employees表:

Employees Table

首先,使用以下select语句从employees表中选择姓氏:

SELECT 
lastname
FROM
employees
ORDER BY
lastname;
+-----------+
| lastname |
+-----------+
| Bondur |
| Bondur |
| Bott |
| Bow |
| Castillo |
| Firrelli |
| Firrelli |
| Fixter |
....
| Jones |

| Patterson |
| Patterson |
| Patterson |
| Thompson |
...
+-----------+
23 rows in set (0.00 sec)

如输出中清楚显示的,一些员工的姓氏相同,例如Bondur、Firrelli。

其次,通过添加DISTINCT子句选择唯一的姓氏,如下所示:

SELECT 
DISTINCT lastname
FROM
employees
ORDER BY
lastname;

从输出中可以清楚地看到,DISTINCT子句从结果集中删除重复的姓氏。

+-----------+
| lastname |
+-----------+
| Bondur |
| Bott |
| Bow |
| Castillo |
| Firrelli |

...
| Nishi |
| Patterson |
| Thompson |
| Tseng |
| Vanauf |
+-----------+
19 rows in set (0.01 sec)

MySQL不区分值和空值

当您在DISTINCT子句中指定一个具有空值的列时,DISTINCT子句将只保留一个空值,因为它认为所有空值都是相同的。

例如,customers表中的state列具有空值。

使用DISTINCT子句查询状态时,将看到DISTINCT states和NULL,如下所示:

SELECT DISTINCT state
FROM customers;
+---------------+
| state |
+---------------+
| NULL |
| NV |
| Victoria |
| CA |
| NY |
| PA |
...
| Co. Cork |
| Pretoria |
| NH |
| Tokyo |
+---------------+
19 rows in set (0.00 sec)

MySQL具有多个列

在DISTINCT子句中指定多列时,DISTINCT子句将使用这些列中的值的组合来确定结果集中行的唯一性。

例如,要从customers表中获取城市和州的唯一组合,可以使用以下查询:

SELECT DISTINCT
state, city
FROM
customers
WHERE
state IS NOT NULL
ORDER BY
state,
city;
+---------------+----------------+
| state | city |
+---------------+----------------+
| BC | Tsawassen |
| BC | Vancouver |
| CA | Brisbane |
| CA | Burbank |
| CA | Burlingame |
| CA | Glendale |
| CA | Los Angeles |
| CA | Pasadena |
| CA | San Diego |
...

如果没有DISTINCE子句,您将获得州和市的重复组合,如下所示:

SELECT 
state, city
FROM
customers
WHERE
state IS NOT NULL
ORDER BY
state ,
city;
+---------------+----------------+
| state | city |
+---------------+----------------+
| BC | Tsawassen |
| BC | Vancouver |
| CA | Brisbane |
| CA | Burbank |
..
| CA | San Francisco |
| CA | San Francisco |
...
| MA | Boston |
| MA | Boston |
| MA | Brickhaven |
| MA | Brickhaven |
| MA | Brickhaven |
...
| NY | NYC |
| NY | NYC |
| NY | NYC |
| NY | NYC |
| NY | NYC |
...

总结