Methods for eliminating duplicate rows in MySQL

Someone in the group asked a question today.

I have a table named test, which contains three fields A, B, and C. Now field B has a lot of duplicate data. Some of these duplicate data fields are empty, some have data, and the C field is correct. Excuse me: I want to remove duplicates Based on the B field. If the two data fields are the same, and the field is empty, if there is data in the other field, delete the data with the null field a. If all fields a have values, simply delete one. How can I write this SQL statement?

According to the requirements, I designed the following table

According to the table, I have the following requirements:

1, 5, 6, 7, leave one; 3, 2, delete one; leave four

Select * From myuser M1, myuser m2 where (m1. B = m2. B) or (m1.a = m2.a) or (m1.a is null) and (m2.a is null )))) group by m1. B

The preceding SQL statement finds all non-repeated items.

When searching for information, you can see the following article. Click to open the link.

/* Some methods to remove duplicate rows in MySQL --- Chu minfei --- 22:49:44. 660 -- reference reprinted please indicate the source: Using ---------------------- -- 1 Use table replacement to delete duplicate items create table test_1 (ID int, value INT ); insert test_1 select 1, 2, Union all select 1, 2, Union all select 2, 3; -- Create Table TMP like test_1, a temporary table with the same null structure as the source table; -- insert non-repeated records into the temporary table insert TMP select distinct * From test_1; -- delete the original table drop table test _ 1; -- change the temporary table name to the target table rename table tmp to test_1; -- display mysql> select * From test_1; + ------ + ------- + | ID | value | + ------ + ------- + | 1 | 2 | 2 | 3 | + ------ + ------- + -- 2. create Table test_1 (ID int, value INT) engine = MyISAM; insert test_1 select 1, 2 Union all select 1, 2 Union all select 2, 3; alter table test_1 add Id2 int not null auto_increment, add primary key (ID, value, Id2); select * From test_1; + ---- + ------- + ----- + | ID | value | Id2 | + ---- + ------- + ----- + | 1 | 2 | 1 | 1 | 2 | 2 | 2 | 3 | 1 | + ---- + ------- + ----- + Delete from test_1 where Id2 <> 1; alter table test_1 drop Id2; select * From test_1; + ---- + ------- + | ID | value | + ---- + ------- + | 1 | 2 | 2 | 3 | + ---- + ------- + ------------------- some fields are repeated --------------------- 1. create Table test_2 (ID int, VA Lue INT); insert test_2 select 1, 2 Union all select 1, 3 Union all select 2, 3; Alter ignore table test_2 add primary key (ID); select * From test_2; + ---- + ------- + | ID | value | + ---- + ------- + | 1 | 2 | 2 | 3 | + ---- + ------- + we can see that the record 1 3 disappears. you can also use the unique constraint here because the column may have a null value, but here there are multiple null values .. -- 2. joint table deletion create table test_2 (ID int, value INT); insert test_2 select 1, 2 Union all select 1, 3 Union all selec T 2, 3; delete a from test_2 a join (select max (value) as V, ID from test_2 group by ID) B on. id = B. ID and. value <> B. v; select * From test_2; + ------ + ------- + | ID | value | + ------ + ------- + | 1 | 3 | 2 | 3 | + ------ + ------- + -- 3. using increment_auto can also be the second method for removing duplicates from all the above fields -- 4. an easy-to-error method-some friends may think of the subquery method. Let's test create table test_2 (ID int, value INT ); insert test_2 select 1, 2 Union all select 1, 3 Union all select 2, 3; delete a from test_2 A where exists (select * From test_2 where. id = ID and. value <value);/* error 1093 (hy000): You can't specify target table 'A' for update in from clause */Currently, You cannot delete a table, at the same time, select from the same table in the subquery. ------------------ Delete a specific duplicate row ------------ -- mainly through order by + limit or directly limit create table test_3 (ID int, value INT ); insert test_3 select 1, 2 Union all select 1, 3 Union all select 1, 4 union all select 2, 3; -- this is the record with the smallest id = 1 value to be retained, delete records with other IDs: delete from test_3 where id = 1 order by value DESC limit 2; select * From test_3; + ------ + ------- + | ID | value | + ------ + ------- + | 1 | 2 | 2 | 3 | + ------ + ------- + if you only want to delete any record and keep one record order by can be removed.