oracle 查重及处理(可保存最旧或最新数据)

最新推荐文章于 2025-07-21 19:22:34 发布

原创最新推荐文章于 2025-07-21 19:22:34 发布 · 770 阅读

1 ·

CC 4.0 BY-SA版权

文章标签：

#sql

本文详细介绍了使用SQL查询和处理数据库中重复数据的方法，包括查询所有重复数据、仅保留首条记录并删除其余重复项、标记重复数据以及更新重复记录的状态。通过实际SQL语句示例，帮助读者掌握高效的数据清理策略。

查询所有重复数据的结果

SELECT ID,DWMC,ADD_TIME
FROM 表名
WHERE DWMC IN (SELECT DWMC FROM 表名 GROUP BY DWMC HAVING COUNT(DWMC) > 1)

查询重复数据只保存,除了首条的结果

SELECT ID,DWMC,ADD_TIME
FROM 表名
WHERE DWMC IN (SELECT DWMC FROM 表名 GROUP BY DWMC HAVING COUNT(DWMC) > 1)
AND ADD_TIME NOT IN (
SELECT MIN(ADD_TIME) FROM 表名 WHERE DWMC IN (SELECT DWMC FROM 表名 GROUP BY DWMC HAVING COUNT(DWMC) > 1) GROUP BY DWMC
)

要保留最后一条的话，把MIN 改成MAX

删除重复数据，仅保存首条

delete from 表名 where id in (
SELECT ID,DWMC,ADD_TIME
FROM 表名
WHERE DWMC IN (SELECT DWMC FROM 表名 GROUP BY DWMC HAVING COUNT(DWMC) > 1)
AND ADD_TIME NOT IN (
SELECT MIN(ADD_TIME) FROM 表名 WHERE DWMC IN (SELECT DWMC FROM 表名 GROUP BY DWMC HAVING COUNT(DWMC) > 1) GROUP BY DWMC
)
)

打上重复标签

update 表名 set DWMC=concat(DWMC,'(重复)') where id in (
SELECT ID
FROM 表名
WHERE DWMC IN (SELECT DWMC FROM 表名 GROUP BY DWMC HAVING COUNT(DWMC) > 1)
AND ADD_TIME NOT IN (
SELECT MIN(ADD_TIME) FROM 表名 WHERE DWMC IN (SELECT DWMC FROM 表名 GROUP BY DWMC HAVING COUNT(DWMC) > 1) GROUP BY DWMC
)
) AND DWMC NOT LIKE '%(重复)%'