SQL查询重复数据和清除重复数据

SQL查询重复数据和清除重复数据 分类: SQL 2008-05-20 11:03 34086人阅读 评论(1) 收藏 举报 sqlsqlserversunjoin 选择重复，消除重复和选择出序列 有例表:emp emp_no name age 001 Tom 17 002 Sun 14 003 Tom 15 004 Tom 16 要求:列出所有名字重复的人的记录 (1)最直观的思路:要知道所有名字有重复人资料，首先必须知道哪个名字重复了: select name from emp group by name having count(*)>1 所有名字重复人的记录是: select * from emp where name in (select name from emp group by name having count(*)>1) (2)稍微再聪明一点，就会想到，如果对每个名字都和原表进行比较，大于2个人名字与这条记录相同的就是合格的 ，就有 select * from emp where (select count(*) from emp e where e.name=emp.name) >1 注意一下这个>1，想下如果是 =1，如果是 =2 如果是>2 如果 e 是另外一张表 而且是=0那结果 就更好玩了:) 这个过程是 在判断工号为001的 人 的时候先取得 001的 名字(emp.name) 然后和原表的名字进行比较 e.name 注意e是emp的一个别名。 再稍微想得多一点，就会想到，如果有另外一个名字相同的人工号不与她他相同那么这条记录符合要求: select * from emp where exists (select * from emp e where e.name=emp.name and e.emp_no<>emp.emp_no) 此思路的join写法: select emp.* from emp,emp e where emp.name=e.name and emp.emp_no<>e.emp_no /* 这个语句较规范的 join 写法是 select emp.* from emp inner join emp e on emp.name=e.name and emp.emp_no<>e.emp_no 但个人比较倾向于前一种写法，关键是更清晰 */ b、有例表:emp name age Tom 16 Sun 14 Tom 16 Tom 16 清除重复 过滤掉所有多余的重复记录 (1)我们知道distinct、group by 可以过滤重复，于是就有最直观的 select distinct * from emp 或 select name,age from emp group by name,age 获得需要的数据，如果可以使用临时表就有解法: select distinct * into #tmp from emp delete from emp insert into emp select * from #tmp (2)但是如果不可以使用临时表，那该怎么办, 我们观察到我们没办法区分数据(物理位置不一样，对 SQL Server来说没有任何区别)，思 路自然是想办法把数据区分出来了，既然现在的所有的列都没办法区分数据，唯一的办法就 是再加个列让它区分出来，加什么列好,最佳选择是identity列: alter table emp add chk int identity(1,1) 表示例: name age chk Tom 16 1 Sun 14 2 Tom 16 3 Tom 16 4 重复记录可以表示为: select * from emp where (select count(*) from emp e where e.name=emp.name)>1 要删除的是: delete from emp where (select count(*) from emp e where e.name=emp.name and e.chk>=emp.chk)>1 再把添加的列删掉，出现结果。 alter table emp drop column chk (3)另一个思路: 视图 select min(chk) from emp group by name having count(*) >1 获得有重复的记录chk最小的值,于是可以 delete from emp where chk not in (select min(chk) from emp group by name) 写成join的形式也可以: (1)有例表:emp emp_no name age 001 Tom 17 002 Sun 14 003 Tom 15 004 Tom 16 要求生成序列号 (1)最简单的方法，根据b问题的解法: alter table emp add chk int identity(1,1) 或 select *,identity(int,1,1) chk into #tmp from emp 如果需要控制顺序怎么办, select top 100000 *,identity(int,1,1) chk into #tmp from emp order by age (2) 假如不可以更改表结构，怎么办, 如果不可以唯一区分每条记录是没有办法的，在可以唯一区分每条记录的时候,可以使用a 中的count的思路解决这个问题 select emp.*,(select count(*) from emp e where e.emp_no<=emp.emp_no) from emp order by (select count(*) from emp e where e.emp_no<=emp.emp_no) 查找所有重复标题的记录: 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) 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) 、查找表中多余的重复记录(多个字段)，不包含rowid最小的记录 5 复制代码 代码如下: 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) (二) 比方说 在A表中存在一个字段“name”， 而且不同记录之间的“name”值有可能会相同， 现在就是需要查询出在该表中的各记录之间，“name”值存在重复的项; 复制代码 代码如下: Select Name,Count(*) From A Group By Name Having Count(*) > 1 如果还查性别也相同大则如下: 复制代码 代码如下: Select Name,sex,Count(*) From A Group By Name,sex Having Count(*) > 1 (三) 方法一 复制代码 代码如下: declare @max integer,@id integer declare cur_rows cursor local for select 主字段,count(*) from 表名 group by 主字段 having count(*) >; 1 open cur_rows fetch cur_rows into @id,@max while @@fetch_status=0 begin select @max = @max -1 set rowcount @max delete from 表名 where 主字段 = @id fetch cur_rows into @id,@max end close cur_rows set rowcount 0 方法二 有两个意义上的重复记录，一是完全重复的记录，也即所有字段均重复的记录，二是部分关键字段重 复的记录，比如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) 最后一个select即得到了Name，Address不重复的结果集(但多了一个autoID字段，实际写时可以写在select子句中省去此列) (四)查询重复 复制代码 代码如下: select * from tablename where id in ( select id from tablename group by id having count(id) > 1 )