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

原创于 2018-01-16 16:41:03 发布 · 1.1k 阅读

0 ·

CC 4.0 BY-SA版权

php 专栏收录该内容

27 篇文章

订阅专栏

本文介绍了一种使用 SQL 语句来查找并删除数据库表中重复记录的方法。通过多个 SQL 查询步骤，可以有效地识别出具有相同 originalcode 的多条记录，并最终保留每组重复项中的最小 id 记录，其余重复项将被删除。此方法适用于需要清理数据以去除重复项的情况。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

-- 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