Shulou(Shulou.com)06/01 Report--

First of all, let's analyze the fact that our aim now is to query all the data in these two tables, and then delete the duplicate records and keep only one piece of data.

Step 1: query the duplicate records of the following two tables (duplicate key fields > 1) ks_examcity, ks_examdistrictselect * from ks_examcity group by examSubjectID,city,province having count (examSubjectID) > 1 * select * from ks_examdistrict group by examSubjectID,district,city having count (examSubjectID) > 1 Step 2: query the first record of each record in the two tables (the first id in each record is minimum) select min (id) from ks_examcity group by examSubjectID, city, provincehaving count (examSubjectID) > 1SELECT min (id) FROM `ks_ examdistrict` GROUP BY `examSubjectID`, `district`, `city`HAVING COUNT (`examSubjectID`) > 1 step 3: query all duplicate data and select `examSubjectID` from ks_examcity group by examSubjectID, city for data other than the first item in the duplicate record Provincehaving count (examSubjectID) > 1) and id not in (select min (id) from ks_examcity group by examSubjectID, city, provincehaving count (examSubjectID) > 1SELECT `examSubjectID` from `ks_ examdistrict` group by `examSubjectID`, `district`, `city`HAVING COUNT (`examSubjectID`) > 1) and id not in (SELECT min (id) FROM `ks_ examdistrict` GROUP BY `examSubjectID`, `district` `city`HAVING COUNT (`examSubjectID`) > 1 step 4: query all id of the data queried above and delete delete from `examcity` where id IN (select id from (select id from ks_examcity where `examjectID` in (select `ex amSubjectID` from ks_examcity group by examSubjectID, city, provincehaving count (examSubjectID) > 1) and id not in (select min (id) from ks_examcity group by examSubjectID, city) using the queried id as a condition Provincehaving count (examSubjectID) > 1) as tmpresult) = = DELETE FROM `ks_ examdistrict` where id IN (SELECT id from (select id from `ks_ examdistrict` where `examSubjectID` in (SELECT `examSubjectID` from `ks_ examdistrict` group by `examSubjectID`, `district`, `city`HAVING COUNT (`examSubjectID`) > 1) and id not in (SELECT min (id) FROM `ks_ examdistrict` GROUP BY `examSubjectID`, `district`, `city`HAVING COUNT (`examSubjectID`) > 1) as tmpresult)

Reference article: https://www.cnblogs.com/jdbeyond/p/8157224.html

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.