Home >Database >Mysql Tutorial >How to remove duplicate data in MySQL?
This article mainly introduces the relevant information of MySQL Detailed explanation of duplicate data instances. Friends in need can refer to
MySQL Detailed explanation of duplicate data instances
There are two senses of duplicate records. One is a completely duplicate record, that is, all fields are repeated. The other is a record with some fields repeated. For the first type of duplication, it is relatively easy to solve. Just 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 a unique index column to the table.
select distinct * from t;
For the second type of duplicate problem, it is usually required to query any record among the duplicate records. Assume that table t has three fields: id, name, and address. id is the primary key, and the duplicate fields are name and address. It is required to obtain a unique result set of these two fields.
-- Oracle, MySQL, use correlated subquery
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 FROM clause #Use subquery, the subquery must have a name, and the column 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
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;
The above is the detailed content of How to remove duplicate data in MySQL?. For more information, please follow other related articles on the PHP Chinese website!