Home  >  Article  >  Database  >  How to remove duplicate data in MySQL?

How to remove duplicate data in MySQL?

零下一度
零下一度Original
2017-06-29 15:42:371400browse

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

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;

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!

Statement:
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn