使用MySQL DISTINCT来去除重复行记录

Summary: in this tutorial, you will learn how to use MySQL DISTINCT operatorwith the SELECT statement to eliminate duplicate rows in the result set.

When querying data from a table, you may get duplicate rows. In order to remove the duplicate rows, you use the DISTINCT operator in the SELECT statement. The syntax of using the DISTINCT operator is as follows:

SELECT DISTINCT columns FROM table_name WHERE where_conditions

Let’s take a look a simple example of using the DISTINCT operator to select the distinct last names of employees from the employees table.

First, we query the last names of employees from the employees table using the SELECT statement as follows:

SELECT lastname FROM employees ORDER BY lastname

Some employees has the same last name  Bondur, Firrelli, etc. To remove the duplicate last names, you use the DISTINCT operator in the SELECT clause as follows:

SELECT DISTINCT lastname FROM employees ORDER BY lastname

The duplicate last names are eliminated in the result set when we use the DISTINCT operator.

MySQL DISTINCT and NULL values

If a column has NULL values and you use the DISTINCT operator for that column, MySQL will keep one NULLvalue and eliminate the other because the DISTINCT operator treats all NULL values as the same value.

For example, in the customers table, we have many rows with state column has NULL values. When we use the DISTINCT operator to query states of customers, we will see distinct states plus a NULL value as the following query:

SELECT DISTINCT state FROM customers

MySQL DISTINCT with multiple columns

You can use the DISTINCT operator with more than one column. The combination of all columns will be used to define the uniqueness of the row in the result set.

For example, to get the unique combination of city and state from the customers table, you use the following query:

SELECT DISTINCT state, city FROM customers WHERE state IS NOT NULL ORDER BY state, city

Without the DISTINCT operator, you will get duplicate combination state and city as follows:

SELECT state, city FROM customers WHERE state IS NOT NULL ORDER BY state, city

DISTINCT vs. GROUP BY Clause

If you use the GROUP BY clause in the SELECT statement without using aggregate functions, the GROUP BY clause will behave like the DISTINCT operator. The following queries produce the same result set:

SELECT DISTINCT state FROM customers; SELECT state FROM customers GROUP BY state;

The difference between DISTINCT operator and GROUP BY clause is that the GROUP BY clause sorts the result set whereas the DISTINCT operator does not.

MySQL DISTINCT and COUNT aggregate function

The DISTINCT operator is used with the COUNT function to count unique records in a table. In this case, it ignores the NULL values. For example, to count the unique states of customers in the U.S., you use the following query:

SELECT COUNT(DISTINCT state) FROM customers WHERE country = 'USA';

In this tutorial, we have shown you various ways of using MySQL DISTINCT operator such as eliminating duplicate records and counting non-NULL values.

Related Tutorials

• Using MySQL SELECT Statement to Query Data

• Querying Data with MySQL IN Operator

• MySQL BETWEEN Operator Explained

• MySQL INNER JOIN

• Using MySQL LIKE Operator to Select Data Based On Patterns

• Combining Result Sets by Using MySQL UNION

• Using MySQL LIMIT

原文链接：http://outofmemory.cn/mysql/mysql-distinct