MySQL distinct and group by filter delete duplicate rows

Let's take a look at the following examples:

The code is as follows	Copy Code
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 on two fields, that is, the ID must be the same as name will be excluded ...

We will change the query statement:

Select ID, distinct name from table

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

It's simple, but there are some places that can't do what we need, and here's a list of common duplicate-action statements.

Ways to query and delete duplicate records
A
1, look for redundant records in the table, duplicate records are based on a single field (Peopleid) to judge

The code is as follows	Copy Code
SELECT * from People where Peopleid in (select Peopleid from People GROUP by Peopleid has count (Peopleid) > 1)

2, delete redundant records in the table, duplicate records are based on a single field (Peopleid) to judge, leaving only rowid minimal records

The code is as follows	Copy Code
Delete from people where Peopleid in (select Peopleid from People GROUP by Peopleid has count (Peopleid) > 1) and rowID not in (select min (rowid) from people GROUP by Peopleid have Count (Peopleid) >1)

3. Find redundant records in the table (multiple fields)

The code is as follows	Copy Code
SELECT * FROM Vitae a where (A.PEOPLEID,A.SEQ) in (select Peopleid,seq from Vitae GROUP by PEOPLEID,SEQ have count (*) > 1)

4, delete redundant records in the table (multiple fields), leaving only the smallest ROWID records

The code is as follows	Copy Code
Delete from Vitae a where (A.PEOPLEID,A.SEQ) in (select Peopleid,seq from Vitae GROUP by PEOPLEID,SEQ have count (*) > 1) and rowID not in (select min (rowid) from Vitae GROUP by PEOPLEID,SEQ have Count (*) >1)

5, look for redundant records in the table (multiple fields), does not contain the smallest ROWID records

/table>

code is as follows	copy code
select * from Vitae a where (A.PEOPLEID,A.SEQ) in (select Peopleid,seq to Vitae GROUP by PEOPLEID,SEQ has count (*) > 1) and rowID not in (select min (rowid) from Vitae GROUP by PEOPLEID,SEQ have Count (*) >1)