Detailed description of SQL statements for removing duplicate records in mysql

There are many methods to remove duplicate records in mysql, and mysql also has a DISTINCT keyword. Next I will introduce you in detail to remove SQL statements for Repeated Records, for more information, see.

SQL SELECT DISTINCT statement

Syntax:

Select distinct column name FROM table name use DISTINCT keyword

To SELECT all values from the "Company" column, use the SELECT statement:

SELECT Company FROM Orders

To SELECT only different values from the Company column, use the select distinct statement:

The Code is as follows:	Copy code
Select distinct Company FROM Orders Let's take a look at the example below: Table Id name 1 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.

The Code is as follows:	Copy code
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:

The Code is as follows:	Copy code
Select distinct name, id from table The result is: Id name 1 2 B 3 c 4 c 5 B

How does distinct not work? It works, but it also applies to two fields, that is, it must have the same id and name to be excluded.

Suddenly, the ghost machine flashed. Since the group_concat function can be used, can other functions be used?

Use the count function to try it out. I am a success ....... It takes so much time to cry ........ It turns out to be so simple ......

Now release the complete statement:

The Code is as follows:	Copy code
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 .....

Alas, it turned out that mysql was so stupid that I would just lie to him with just a few clicks. I am so depressed (by the way, there is also the guy Rong). Now I hope you will not be overwhelmed by this problem.

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

Supplement:

After my test select *, count (distinct name) from table group by name can be used.

Search for all duplicate data

The Code is as follows:

Copy code

/* Search for all duplicate data */ SELECT 't1 '.* FROM 't1 ',( SELECT 'name', 'add' FROM 't1' Group by 'name', 'add' Having count (1)> 1 ) AS 't2' WHERE 't1'. 'name' = 't2'. 'name' AND 't1'. 'add' = 't2'. 'add '; + ---- + ------ + ----- + | Id | name | add | + ---- + ------ + ----- + | 1 | abc| 123 | | 2 | abc| 123 | | 4 | abc| 123 | | 7 | xzy | 456 | | 6 | xzy | 456 | | 8 | xzy | 456 | | 9 | xzy | 789 | 11 | xzy | 789 | | 12 | ijk | 147 | | 13 | ijk | 147 | 19 | tprimary | 963 | | 20 | tprimary | 963 | | 21 | Lower | 546 | | 22 | Lower | 546 | + ---- + ------ + ----- + Rows in set (0.00 sec)

Search for duplicate data with the smallest id

The Code is as follows:

Copy code

/* Search for duplicate data with the smallest id */ SELECT 't1 '.* FROM 't1 ',( Select distinct min ('id') AS 'id', 'name', 'add' FROM 't1' Group by 'name', 'add' Having count (1)> 1 ) AS 't2' WHERE 't1'. 'name' = 't2'. 'name' AND 't1'. 'add' = 't2'. 'add' AND 't1'. 'id' <> 't2'. 'id '; + ---- + ------ + ----- + | Id | name | add | + ---- + ------ + ----- + | 2 | abc| 123 | | 4 | abc| 123 | | 6 | xzy | 456 | | 8 | xzy | 456 | | 11 | xzy | 789 | | 13 | ijk | 147 | 20 | tprimary | 963 | | 22 | Lower | 546 | + ---- + ------ + ----- + Rows in set (0.00 sec)