删除一张表中重复的值以及查找出表中重复两条以上的数据

时间:2022-09-16 14:48:01
-- SELECT MIN(id) FROM ctydetail GROUP BY originalcode HAVING COUNT(originalcode) > 1
-- SELECT originalcode FROM ctydetail GROUP BY originalcode HAVING COUNT(originalcode) > 1
-- SELECT id FROM ctydetail WHERE originalcode IN ( SELECT originalcode FROM ctydetail GROUP BY originalcode HAVING COUNT(originalcode) > 1 ) AND id NOT IN ( SELECT MIN(id) FROM ctydetail GROUP BY originalcode HAVING COUNT(originalcode) > 1 )
-- SELECT id FROM ctydetail WHERE originalcode IN ( SELECT originalcode FROM ctydetail GROUP BY originalcode HAVING COUNT(originalcode) > 1 )
-- DELETE FROM  ctydetail WHERE originalcode IN ( select originalcode from (SELECT originalcode FROM ctydetail GROUP BY originalcode HAVING COUNT(originalcode) > 1 ) as temp1) AND id NOT IN (select * from ( SELECT MIN(id) FROM ctydetail GROUP BY originalcode HAVING COUNT(originalcode) > 1 ) a)
-- SELECT COUNT(*) AS a FROM ctydetail GROUP BY originalcode ORDER BY a DESC