ORACLE 重复记录相关操作

最新推荐文章于 2025-07-02 00:06:23 发布

原创最新推荐文章于 2025-07-02 00:06:23 发布 · 535 阅读

0 ·

CC 4.0 BY-SA版权

本文介绍了如何使用SQL语句来删除数据库中重复的记录，并提供了两种不同的方法：一种是保留最大ID的记录并删除其余重复项；另一种是保留最小ID的记录并删除其余重复项。此外，还提供了一个具体的实例来展示如何根据特定条件筛选重复记录。

----A:保留id最大的行,删除其它行

--方法1

DELETE USER FROM USER T INNER JOIN (SELECT NAME, MAX(ID) AS ID
FROM USER
GROUP BY NAME) A ON T.NAME = A.NAME AND T.ID <> A.ID

--方法2

DELETE USER FROM USER T
WHERE EXISTS (SELECT *
FROM USER
WHERE NAME = T.NAME
AND ID > T.ID)

----B:保留id最小的行,删除其它行

--方法1

DELETE USER FROM USER T INNER JOIN (SELECT NAME, MIN(ID) AS ID
FROM USER
GROUP BY NAME) A ON T.NAME = A.NAME AND T.ID <> A.ID

--方法2

DELETE USER FROM USER T
WHERE EXISTS (SELECT *
FROM USER
WHERE NAME = T.NAME
AND ID < T.ID)

----C:删除所有重复的name行

DELETE USER FROM USER T INNER JOIN (SELECT ID
FROM USER A
WHERE EXISTS
(SELECT *
FROM USER
WHERE NAME = A.NAME
GROUP BY NAME
HAVING COUNT(*) > 1)) AS B ON T.ID = B.ID

---实例:file_name,length重复记录根据file_id大的值查出:
SELECT T1.*
FROM PICK_LOG T1
WHERE FILE_ID IN (SELECT MAX(FILE_ID)
FROM PICK_LOG T2
WHERE T1.FILE_NAME = T2.FILE_NAME
AND T1.LENGTH = T2.LENGTH
GROUP BY T2.FILE_NAME,T2.LENGTH
HAVING COUNT(*) > 1)