Detailed example of removing duplicate data in MySQL

Detailed example of removing duplicate data in MySQL

There are two meanings of duplicate records: one is a completely duplicate record, that is, all fields are repeated; the other is a record with some fields repeated. The first type of duplication is relatively easy to solve. You only need to use the distinct keyword in the query statement to remove duplicates. Almost all database systems support the distinct operation. The main reason for this duplication is poor table design, which can be avoided by adding a primary key or unique index column to the table.

select distinct * from t;

For the second type of duplicate problem, it is usually required to query any one of the duplicate records. Assume that table t has three fields: id, name, and address. id is the primary key. The repeated fields are name and address. It is required to get a unique result set for these two fields.

-- Oracle, MySQL, using correlated subqueries

select * from t t1

 where t1.id =

 (select min(t2.id)

  from t t2

  where t1.name = t2.name and t1.address = t2.address);

-- Hive only supports subqueries in the FROM clause. Subqueries must have names and columns must be unique.

select t1.*

 from t t1,

   (select name, address, min(id) id from t group by name, address) t2

 where t1.id = t2.id;

 

-- You can also use hive's row_number() analysis function

select t.id, t.name, t.address

 from (select id, name, address,

row_number() over (distribute by name, address sort by id) as rn 

     from t) t 

 where t.rn=1;

Thank you for reading, I hope it can help you, thank you for your support of this site!