Two methods for removing duplicates from mysql and the instance code.

Two methods for removing duplicates from mysql and the instance code.

Mysql deduplication

Method 1:

When using MySQL, you sometimes need to query records with unique fields. Although mysql provides the keyword distinct to filter out redundant duplicate records, only one record is retained, but it is often used to return the number of records that do not repeat, instead of returning all values that do not record the record. The reason is that distinct can only return its target field, but cannot return other fields.

Let's take a look at the example below:

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

The library structure is like this. This is just a simple example, and the actual situation is much more complicated.

For example, if you want to use a statement to query all data with no duplicate names, you must use distinct to remove redundant duplicate records.

select distinct name from table

The result is:

 name  a  b  c

It seems that the effect has been achieved, but what I want to get is the id value? Modify the query statement:

select distinct name, id from table

The result is:

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

How does distinct not work? The role is played, but it also applies to two fields, that is, they must have the same id and name before they can be divided .......

Modify the query statement again:

select id, distinct name from table

Unfortunately, you cannot get anything except the error message. You must start with distinct. Is it difficult to place distinct in the where condition? Error .......

The easy-to-use statements are 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, so you don't have to worry about it. The goal 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 ...... ) Group by name

Method 2:

Use group

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 = 'admin '))) union all select * from customer where source_user = (/* my online 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 = (/* my online user */select 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;

Add an alias. Otherwise, an error is reported. Pack the where statement and use group by to remove the duplicate statement.

Thank you for reading this article. I hope it will help you. Thank you for your support for this site!