重复记录:有两个意义上的重复记录。
一是完全重复的记录,也即所有字段均重复的记录;
select distinct * from tableName 查询无重复记录的结果集
发生这种重复的原因是表设计不周产生的,增加唯一索引列即可解决。
二是部分关键字段重复的记录,比如Name字段,password重复,而其他字段不一定重复或都重复可以忽略。
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最大一条记录
删除重复记录--大的rowid(即保留最小的rowid的那条数据记录)
DELETE FROM TEST_USER A
WHERE (A.USERNAME, A.PASSWORD) IN
(SELECT USERNAME, PASSWORD
FROM TEST_USER
GROUP BY USERNAME, PASSWORD
HAVING COUNT(*) > 1)
AND ROWID NOT IN (SELECT MIN(ROWID)
FROM TEST_USER
GROUP BY USERNAME, PASSWORD
HAVING COUNT(*) > 1)
DELETE FROM TEST_USER A
WHERE (A.USERNAME, A.PASSWORD) IN
(SELECT USERNAME, PASSWORD
FROM TEST_USER
GROUP BY USERNAME, PASSWORD
HAVING COUNT(*) > 1)
AND ROWID NOT IN (SELECT MIN(ROWID)
FROM TEST_USER
GROUP BY USERNAME, PASSWORD
HAVING COUNT(*) > 1)
或者:
2。保留一条(这个应该是大多数人所需要的 ^_^)
Delete HZT Where ID Not In (Select Max(ID) From HZT Group By Title)