在本教程中,您将学习如何使用MySQL DISTINCT子句与SELECT语句一起组合来消除结果集中的重复行。
1. MySQL DISTINCT子句简介
从表中查询数据时,可能会收到重复的行记录。为了删除这些重复行,可以在SELECT语句中使用DISTINCT子句。
DISTINCT子句的语法如下:
SELECT DISTINCT
columns
FROM
table_name
WHERE
where_conditions;
2. MySQL DISTINCT示例
下面来看看一个使用DISTINCT子句从employees表中选择员工的唯一姓氏(lastName)的简单示例。
首先,使用SELECT语句从employees表中查询员工的姓氏(lastName),如下所示:
SELECT
lastname
FROM
employees
ORDER BY lastname;
执行上面查询语句,得到以下结果 -
mysql> SELECT lastname FROM employees ORDER BY lastname;
+-----------+
| lastname |
+-----------+
| Bondur |
| Bondur |
| Bott |
| Bow |
| Castillo |
| Firrelli |
| Firrelli |
| Fixter |
| Gerard |
| Hernandez |
| Jennings |
| Jones |
| Kato |
| King |
| Marsh |
| Murphy |
| Nishi |
| Patterson |
| Patterson |
| Patterson |
| Thompson |
| Tseng |
| Vanauf |
+-----------+
23 rows in set
可看到上面结果中,有好些结果是重复的,比如:Bondur,Firrelli等,那如何做到相同的结果只显示一个呢?要删除重复的姓氏,请将DISTINCT子句添加到SELECT语句中,如下所示:
SELECT DISTINCT
lastname
FROM
employees
ORDER BY lastname;
执行上面查询,得到以下输出结果 -
mysql> SELECT DISTINCT lastname FROM employees ORDER BY lastname;
+-----------+
| lastname |
+-----------+
| Bondur |
| Bott |
| Bow |
| Castillo |
| Firrelli |
| Fixter |
| Gerard |
| Hernandez |
| Jennings |
| Jones |
| Kato |
| King |
| Marsh |
| Murphy |
| Nishi |
| Patterson |
| Thompson |
| Tseng |
| Vanauf |
+-----------+
19 rows in set
当使用DISTINCT子句时,重复的姓氏(lastname)在结果集中被消除。
3. MySQL DISTINCT和NULL值
如果列具有NULL值,并且对该列使用DISTINCT子句,MySQL将保留一个NULL值,并删除其它的NULL值,因为DISTINCT子句将所有NULL值视为相同的值。
例如,在customers表中,有很多行的州(state)列是NULL值。 当使用DISTINCT子句来查询客户所在的州时,我们将看到唯一的州和NULL值,如下查询所示:
SELECT DISTINCT
state
FROM
customers;
执行上面查询语句后,输出结果如下 -
mysql> SELECT DISTINCT state FROM customers;
+---------------+
| state |
+---------------+
| NULL |
| NV |
| Victoria |
| CA |
| NY |
| PA |
| CT |
| MA |
| Osaka |
| BC |
| Qubec |
| Isle of Wight |
| NSW |
| NJ |
| Queensland |
| Co. Cork |
| Pretoria |
| NH |
| Tokyo |
+---------------+
19 rows in set
4. MySQL DISTINCT在多列上的使用
可以使用具有多个列的DISTINCT子句。 在这种情况下,MySQL使用所有列的组合来确定结果集中行的唯一性。
例如,要从customers表中获取城市(city)和州(state)的唯一组合,可以使用以下查询:
SELECT DISTINCT
state, city
FROM
customers
WHERE
state IS NOT NULL
ORDER BY state , city;
执行上面查询,得到以下结果 -
mysql> 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 |
| CA | San Francisco |
| CA | San Jose |
| CA | San Rafael |
| Co. Cork | Cork |
| CT | Bridgewater |
| CT | Glendale |
| CT | New Haven |
| Isle of Wight | Cowes |
| MA | Boston |
| MA | Brickhaven |
| MA | Cambridge |
| MA | New Bedford |
| NH | Nashua |
| NJ | Newark |
| NSW | Chatswood |
| NSW | North Sydney |
| NV | Las Vegas |
| NY | NYC |
| NY | White Plains |
| Osaka | Kita-ku |
| PA | Allentown |
| PA | Philadelphia |
| Pretoria | Hatfield |
| Qubec | Montral |
| Queensland | South Brisbane |
| Tokyo | Minato-ku |
| Victoria | Glen Waverly |
| Victoria | Melbourne |
+---------------+----------------+
37 rows in set
没有DISTINCT子句,将查询获得州(state)和城市(city)的重复组合如下:
SELECT
state, city
FROM
customers
WHERE
state IS NOT NULL
ORDER BY state , city;
执行上面查询,得到以下结果 -
5. DISTINCT子句与GROUP BY子句比较
如果在SELECT语句中使用GROUP BY子句,而不使用聚合函数,则GROUP BY子句的行为与DISTINCT子句类似。
以下语句使用GROUP BY子句来选择customers表中客户的唯一state列的值。
SELECT
state
FROM
customers
GROUP BY state;
执行上面查询,得到以下结果 -
mysql> SELECT state FROM customers GROUP BY state;
+---------------+
| state |
+---------------+
| NULL |
| BC |
| CA |
| Co. Cork |
| CT |
| Isle of Wight |
| MA |
| NH |
| NJ |
| NSW |
| NV |
| NY |
| Osaka |
| PA |
| Pretoria |
| Qubec |
| Queensland |
| Tokyo |
| Victoria |
+---------------+
19 rows in set
可以通过使用DISTINCT子句来实现类似的结果:
mysql> SELECT DISTINCT state FROM customers;
+---------------+
| state |
+---------------+
| NULL |
| NV |
| Victoria |
| CA |
| NY |
| PA |
| CT |
| MA |
| Osaka |
| BC |
| Qubec |
| Isle of Wight |
| NSW |
| NJ |
| Queensland |
| Co. Cork |
| Pretoria |
| NH |
| Tokyo |
+---------------+
19 rows in set
一般而言,DISTINCT子句是GROUP BY子句的特殊情况。 DISTINCT子句和GROUP BY子句之间的区别是GROUP BY子句可对结果集进行排序,而DISTINCT子句不进行排序。
如果将ORDER BY子句添加到使用DISTINCT子句的语句中,则结果集将被排序,并且与使用GROUP BY子句的语句返回的结果集相同。
SELECT DISTINCT
state
FROM
customers
ORDER BY state;
执行上面查询,得到以下结果 -
mysql> SELECT DISTINCT state FROM customers ORDER BY state;
+---------------+
| state |
+---------------+
| NULL |
| BC |
| CA |
| Co. Cork |
| CT |
| Isle of Wight |
| MA |
| NH |
| NJ |
| NSW |
| NV |
| NY |
| Osaka |
| PA |
| Pretoria |
| Qubec |
| Queensland |
| Tokyo |
| Victoria |
+---------------+
19 rows in set
6. MySQL DISTINCT和聚合函数
可以使用具有聚合函数(例如SUM,AVG和COUNT)的DISTINCT子句中,在MySQL将聚合函数应用于结果集之前删除重复的行。
例如,要计算美国客户的唯一state列的值,可以使用以下查询:
SELECT
COUNT(DISTINCT state)
FROM
customers
WHERE
country = 'USA';
执行上面查询,得到以下结果 -
mysql> SELECT COUNT(DISTINCT state) FROM customers WHERE country = 'USA';
+-----------------------+
| COUNT(DISTINCT state) |
+-----------------------+
| 8 |
+-----------------------+
1 row in set
7. MySQL DISTINCT与LIMIT子句
如果要将DISTINCT子句与LIMIT子句一起使用,MySQL会在查找LIMIT子句中指定的唯一行数时立即停止搜索。
以下查询customers表中的前3个非空(NOT NULL)唯一state列的值。
mysql> SELECT DISTINCT state FROM customers WHERE state IS NOT NULL LIMIT 3;
+----------+
| state |
+----------+
| NV |
| Victoria |
| CA |
+----------+
3 rows in set
在本教程中,我们学习了使用MySQL DISTINCT子句的各种方法,例如消除重复行和计数非NULL值。
¥ 我要打赏
纠错/补充
收藏
加QQ群啦,易百教程官方技术学习群
注意:建议每个人选自己的技术方向加群,同一个QQ最多限加 3 个群。