查找删除重复记录的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)