Detailed Introduction to the MySQL Keyword Distinct

Introduction to the usage of MySQL keyword Distinct

DDL

Prepare SQL:

create table test(id bigint not null primary key auto_increment,
   name varchar(10) not null,
   phone varchar(10) not null,
   email varchar(30) not null)engine=innodb;

Prepare Data:

 insert into test(name, phone, email)values('alibaba','0517','[email protected]');
  insert into test(name, phone, email)values('alibaba','0517','[email protected]');
  insert into test(name, phone, email)values('baidu','010','[email protected]');
  insert into test(name, phone, email)values('tencent','0755','[email protected]');
  insert into test(name, phone, email)values('vipshop','020','[email protected]');
  insert into test(name, phone, email)values('ctrip','021','[email protected]');
  insert into test(name, phone, email)values('suning','025','[email protected]');

The query data is shown in the following figure:

In the first case, use the Distinct keyword to query single-column data, as shown in the following figure:

Result: The name field is deduplicated, which meets expectations. The duplicate field value alibaba is indeed filtered out.

In the second case, use the Distinct keyword (in front) to query multiple columns of data, as shown in the following figure:

Result: The name field is deduplicated, but the result does not meet expectations. The duplicate field value alibaba is not filtered out.

In the second case, use the Distinct keyword (after) to query multiple columns of data, as shown in the following figure:

Result: The name field is deduplicated, but the result does not meet expectations. A SQL exception is thrown with error code 1064.

Solution:

Don't use subqueries, use grouping to solve it:

Summary: If the SQL query can be solved with one statement, try not to increase the complexity of SQL, especially subqueries! ! !

The above is an explanation of the usage of the MySQL keyword Distinct. If you have any questions, please leave a message or discuss on this site. Thank you for reading and I hope it can help everyone. Thank you for your support of this site!