MySQL remove duplicate record SQL statement detailed

SQL SELECT DISTINCT Statement

Grammar:

SELECT DISTINCT column name from table name using DISTINCT keyword

If you want to select all the values from the company column, we need to use the SELECT statement:

SELECT Company from Orders

To select only a different value from the company column, we need to use the Select DISTINCT statement:

The code is as follows	Copy Code
SELECT DISTINCT Company from Orders Let's take a look at the following examples: Table ID Name 1 A 2 b 3 C 4 C 5 b

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

For example, I want to use a statement query to get the name does not duplicate all the data, it must use distinct to remove unnecessary duplicate records.

The code is as follows	Copy Code
Select DISTINCT name from table The results obtained are: Name A B C

It seems to be working, but what I want to get is the ID value? Change the query statement:

The code is as follows	Copy Code
Select DISTINCT name, ID from table The result will be: ID Name 1 A 2 b 3 C 4 C 5 b

Why didn't distinct play a role? The effect is up, but he also works with two fields, that is, the ID must be the same as name to be excluded

A sudden flash of a mind, since the GROUP_CONCAT function can be used, that other functions can do?

Use the Count function to try, success, I ... Want to cry ah, so much time. It was so simple ...

Now release the full statement:

The code is as follows	Copy Code
SELECT *, COUNT (distinct name) from table group by name Results: ID Name count (distinct name) 1 a 1 2 B 1 3 C 1

The last one is superfluous, do not need to control on the line, to achieve ...

Alas, the original MySQL so stupid, gently put him fooled past, depressed also on me (right, there are also allow that fellow), now take out hope that we do not be the problem toss.

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


Add:

After I test select *, COUNT (distinct name) from table group by name is OK.

Find all duplicate data

code is as follows

copy code

/* Find 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 | | 6 | Xzy | 456 | | 7 | Xzy | 456 | | 8 | Xzy | 456 | | 9 | Xzy | 789 | | 11 | Xzy | 789 | | 12 | Ijk | 147 | | 13 | Ijk | 147 | | 19 | TPK | 963 | | 20 | TPK | 963 | | 21 | Wer | 546 | | 22 | Wer | 546 | +----+------+-----+ Rows in Set (0.00 sec)

Find duplicate data In addition to the least ID

code is as follows

copy code

/* Find duplicates except for the least ID data Data */ Select ' T1 '. * from ' T1 ', (   Select DISTINCT MIN (' id ') as ' id ', ' name ', ' Add '   from ' T1 '   GROUP by ' name ', ' Add '   has COUNT (1) > 1 ) as ' T2 ' WHERE ' t1 ', ' name ' = ' T2 '. ' Name ' &nbs P and ' T1 '. ' Add ' = ' t2 '. ' Add '   and ' T1 '. ' id ' <> ' t2 '. ' id '; +----+------+-----+ | id | name | add | +----+------+-----+ |  2 | abc  | 123 | |  4 | abc  | 123 | |  7 | xzy  | 456 | |  8 | xzy  | 456 | | xzy  | 789 | | ijk  | 147 | | tpk  | 963 | | wer  | 546 | +----+------+-----+  rows in Set (0.00 sec)