I recently helped the Ops team with a rush of data that was on the shelf, about 1.4 million volumes, so let's look at how it was handled.
Most of this data is duplicated, and the processed data is around 300,000, and then a one-to-many merge process is performed.
The following provides two methods or ideas, the first idea is to provide more of a kind of online, that is, through the uniqueness of the constraints on the field for sorting, and then query processing out of these data are duplicates of the first data, so the id is bound to be the smallest of these duplicates, and then delete these duplicates of the id is not the smallest of these duplicates can be derived from the screening results.
Of course the data above the design to commercial confidentiality, here I will create a demo myself.
The sql is as follows:
-
// Filter out the duplicate data with the smallest id and delete it in the second step
-
select id FROM haha
-
where CONCAT(namea,sex,num) in (
-
SELECT CONCAT(namea,sex,num) FROM haha
-
GROUP BY CONCAT(namea,sex,num)
-
HAVING count(id> 1)
-
)
-
-
//Perform a delete operation to remove redundant data
-
DELETE FROM haha where id in (
-
select id FROM haha
-
where CONCAT(namea,sex,num) in (
-
SELECT CONCAT(namea,sex,num) FROM haha
-
GROUP BY CONCAT(namea,sex,num)
-
HAVING count(id> 1)
-
)
-
and id not in (
-
SELECT MIN(id) as id FROM haha
-
GROUP BY CONCAT(namea,sex,num)
-
HAVING COUNT(id > 1)
-
))
Of course there were some errors encountered in writing the sql.
1, select * from haha where id in (select id , namea from haha ) select in the right side can only be id, so namea should be removed, in addition, name in theMySqlThe keyword is displayed in the field, so be careful when naming this field to avoid causing unnecessary trouble or errors.
2、ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your
This error is saying that there is an error in the symbols in the sql statement, so we need to check the punctuation, whether the comma is in English or not, and then whether the parentheses are too much or too little
3、To note the place, MySql does not allow direct deletion of the query out of the data, so we query out of the data to be stored in a temporary table in the delete
The second method, because we know that the final result of the data to be processed is about 300,000, which for 1.4 million, go to delete the remaining 1.1 million data, obviously we only need to focus on the 300,000 data can be, so we directly import the query out of the data into a new table, the
-
create table finish as
-
select * FROM haha
-
where CONCAT(namea,sex,num) in (
-
SELECT CONCAT(namea,sex,num) FROM haha
-
GROUP BY CONCAT(namea,sex,num)
-
HAVING count(id> 1)
-
)
The data is then processed 1-to-many:
The original data looked like this:
The results are as follows:
