继续浏览精彩内容
慕课网APP
程序员的梦工厂
打开
继续
感谢您的支持,我会继续努力的
赞赏金额会直接到老师账户
将二维码发送给自己后长按识别
微信支付
支付宝支付

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

守着星空守着你
关注TA
已关注
手记 211
粉丝 39
获赞 266

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

Duplicate last name

Some employees has the same last name  BondurFirrelli, 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

MySQL Distinct 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 NULL value

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

MySQL DISTINCT multiple columns

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

MySQL without DISTINCT operator

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';

MySQL Distinct Count

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

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

打开App,阅读手记
0人推荐
发表评论
随时随地看视频慕课网APP