2021-02-20

查找删除重复记录的sql语句

SELECT *

FROM T_INFO A

WHERE ((SELECT COUNT(*)

          FROM T_INFO

          WHERE TITLE = A.TITLE) > 1)

ORDER BY TITLE DESC

一。查找重复记录

1。查找全部重复记录

SELECT * FROM 表 WHERE 重复字段 IN (SELECT 重复字段 FROM 表 GROUP BY 重复字段 HAVING COUNT(*)>1)

2。过滤重复记录(只显示一条)

SELECT * FROM HZT WHERE ID IN (SELECT MAX(ID) FROM HZT GROUP BY TITLE)

注：此处显示ID最大一条记录

二。删除重复记录

1。删除全部重复记录（慎用）

DELETE 表 WHERE 重复字段 IN (SELECT 重复字段 FROM 表 GROUP BY 重复字段 HAVING COUNT(*)>1)

2。保留一条（这个应该是大多数人所需要的 ^_^）

DELETE HZT WHERE ID NOT IN (SELECT MAX(ID) FROM HZT GROUP BY TITLE)

注：此处保留ID最大一条记录

1、查找表中多余的重复记录，重复记录是根据单个字段（PEOPLEID）来判断

SELECT * FROM PEOPLE

WHERE PEOPLEID IN (SELECT   PEOPLEID FROM   PEOPLE GROUP BY   PEOPLEID HAVING COUNT(PEOPLEID) > 1)

 

2、删除表中多余的重复记录，重复记录是根据单个字段（PEOPLEID）来判断，只留有ROWID最小的记录

DELETE FROM PEOPLE

WHERE PEOPLEID IN (SELECT   PEOPLEID FROM PEOPLE GROUP BY   PEOPLEID   HAVING COUNT(PEOPLEID) > 1)

AND ROWID NOT IN (SELECT MIN(ROWID) FROM   PEOPLE GROUP BY PEOPLEID HAVING COUNT(PEOPLEID )>1)

 

3、查找表中多余的重复记录（多个字段）

SELECT * FROM VITAE A

WHERE (A.PEOPLEID,A.SEQ) IN   (SELECT PEOPLEID,SEQ FROM VITAE GROUP BY PEOPLEID,SEQ HAVING COUNT(*) > 1)

 

4、删除表中多余的重复记录（多个字段），只留有ROWID最小的记录

DELETE FROM VITAE A

WHERE (A.PEOPLEID,A.SEQ) IN   (SELECT PEOPLEID,SEQ FROM VITAE GROUP BY PEOPLEID,SEQ HAVING COUNT(*) > 1)

AND ROWID NOT IN (SELECT MIN(ROWID) FROM VITAE GROUP BY PEOPLEID,SEQ HAVING COUNT(*)>1)

 

5、查找表中多余的重复记录（多个字段），不包含ROWID最小的记录

SELECT * FROM VITAE A

WHERE (A.PEOPLEID,A.SEQ) IN   (SELECT PEOPLEID,SEQ FROM VITAE GROUP BY PEOPLEID,SEQ HAVING COUNT(*) > 1)

AND ROWID NOT IN (SELECT MIN(ROWID) FROM VITAE GROUP BY PEOPLEID,SEQ HAVING COUNT(*)>1)

补充：

有两个以上的重复记录，一是完全重复的记录，也即所有字段均重复的记录，二是部分关键字段重复的记录，比如NAME字段重复，而其他字段不一定重复或都重复可以忽略。

 

1、对于第一种重复，比较容易解决，使用

SELECT DISTINCT * FROM TABLENAME

 

就可以得到无重复记录的结果集。

 

如果该表需要删除重复的记录（重复记录保留1条），可以按以下方法删除

SELECT DISTINCT * INTO #TMP FROM TABLENAME

DROP TABLE TABLENAME

SELECT * INTO TABLENAME FROM #TMP

DROP TABLE #TMP

 

发生这种重复的原因是表设计不周产生的，增加唯一索引列即可解决。

 

2、这类重复问题通常要求保留重复记录中的第一条记录，操作方法如下

 

假设有重复的字段为NAME,ADDRESS，要求得到这两个字段唯一的结果集

SELECT IDENTITY(INT,1,1) AS AUTOID, * INTO #TMP FROM TABLENAME

SELECT MIN(AUTOID) AS AUTOID INTO #TMP2 FROM #TMP GROUP BY NAME,AUTOID

SELECT * FROM #TMP WHERE AUTOID IN(SELECT AUTOID FROM #TMP2)