Detailed examples of two methods for deduplication in mysql

This article mainly introduces mysql the two methods of deduplication and the related information of the example code. Here is a detailed explanation of the two methods of deduplication one by one. Friends who need it can refer to it. Download

mysql to remove duplicates

Method 1:

When using MySQL , sometimes it is necessary to query to find non-duplicate records in a certain field. Although mysql provides the distinct keyword to filter out redundant duplicate records and keep only one, it is often only used to return non-duplicate records. number instead of using it to return all values ​​without duplicate records. The reason is that distinct can only return its target field, but cannot return other fields

Let’s take a look at an example first:

   table
  id name
  1 a
  2 b
  3 c
  4 c
  5 b

The library structure is roughly like this , this is just a simple example, the actual situation will be much more complicated.

For example, if I want to use one statement to query all data with non-duplicate names, I must use distinct to remove redundant duplicate records.

select distinct name from table

The result obtained is:

 name
  a
  b
  c

It seems to have achieved the effect, but what I want to get is the id value? Change the query statement:

select distinct name, id from table

The result will be:

 id name
  1 a
  2 b
  3 c
  4 c
  5 b

Why does distinct not work? It works, but it affects two fields at the same time, that is, the id and name must be the same before they can be deleted. . . . . . .

Let’s change the query statement:

select id, distinct name from table

Unfortunately, you can’t get anything except

error message, distinct must be placed at the beginning. Is it so difficult to put distinct in the where condition? Can report errors. . . . . . .

The final useful statement is as follows:

select *, count(distinct name) from table group by name

Result:

  id name count(distinct name)
  1 a 1
  2 b 1
  3 c 1

The last item is redundant, just leave it alone, the purpose is achieved. . . . .

Oh, yes, by the way, group by must be placed before order by and limit, otherwise an error will be reported. . . . . . . . ! OK

Summary statement:

select *, count(distinct name) from (select * from table...and other nested statements) group by name

Method 2:

Use group by

SELECT * FROM( 
select * from customer where user=( 
  SELECT source_user from customer WHERE user='admin') UNION ALL select * from customer where user=( 
  select source_user from customer where user=( 
    SELECT source_user from customer WHERE user='admin')) union ALL select * from customer where user=( 
  select source_user from customer where user=( 
    select source_user from customer where user=( 
      SELECT source_user from customer WHERE user='admin'))) UNION ALL select * from customer where source_user=(/*我的上线的上线的user*/ 
  select user from customer where user=( 
    select source_user from customer where user=( 
      SELECT source_user from customer WHERE user='admin'))) union all select * from customer where source_user=(/*我的上线的上线的上线user*/ 
  select user from customer where user=( 
  select source_user from customer where user=( 
    select source_user from customer where user=( 
      SELECT source_user from customer WHERE user='admin'))))) as alias group by user;

Be careful to add an alias, otherwise an error will be reported, be sure to wrap it outside the where statement, and then use group by Only deduplication will take effect.

The above is the detailed content of Detailed examples of two methods for deduplication in mysql. For more information, please follow other related articles on the PHP Chinese website!