SQL中重复数据的查询与删除.docx

《SQL中重复数据的查询与删除.docx》由会员分享，可在线阅读，更多相关《SQL中重复数据的查询与删除.docx（16页珍藏版）》请在淘文阁 - 分享文档赚钱的网站上搜索。

1、SQL 中重复数据的查询与删除=第一篇=在一张表中某个字段下面有重复记录，有很多方法，但是有一个方法，是比较高效的，如下语句：select data_guid from adam_entity_datas a where a.rowid (select min(b.rowid) from adam_entity_datas b where b.data_guid = a.data_guid)假设表中有大量数据，但是重复数据比较少，那么可以用下面的语句提高效率select data_guid from adam_entity_datas where data_guid in (select da

2、ta_guid from adam_entity_datas group by data_guid having count(*) 1)此方法查询出全部重复记录了，也就是说，只要是重复的就选出来，下面的语句或许更高效select data_guid from adam_entity_datas where rowid in (select rid from (select rowid rid,row_numberover(partition by data_guid order by rowid) m from adam_entity_datas) where m 1)目前只知道这三种比较有效

3、的方法。第一种方法比较好理解，但是最慢，其次种方法最快，但是选出来的记录是全部重复的记录， 而不是一个重复记录的列表，第三种方法，我认为最好。=其次篇=select usercode,count(*) from ptype group by usercode having count(*) 1=第三篇=找出重复记录的ID:select ID from( select ID ,count(*) as Cnt from 要消退重复的表group by ID) T1where Tt1删除数据库中重复数据的几个方法数据库的使用过程中由于程序方面的问题有时候会遇到重复数据，重复数据导致了数据库局部设置不

4、能正确设置方法一declare max integer,id integerdeclare cur_rows cursor local for select 主字段,count(*) from表名 group by 主字段 having count(*) 1 open cur_rowsfetch cur_rows into id,max while fetch_status=0beginselect max = max -1 set rowcount maxdelete from 表名 where 主字段 = id fetch cur_rows into id,maxendclose cur_

5、rows set rowcount 0方法二有两个意义上的重复记录，一是完全重复的记录，也即全部字段均重复的记录，二是局部关键字段重复的记录，比方Name 字段重复，而其他字段不肯定重复或都重复可以无视。1、对于第一种重复，比较简洁解决，使用select distinct * from tableName就可以得到无重复记录的结果集。假设该表需要删除重复的记录，可以按以下方法删除select distinct * into #Tmp from tableName drop table tableNameselect * into tableName from #Tmp drop table #

6、Tmp2、这类重复问题通常要求保存重复记录中的第一条记录，*作方法如下假设有重复的字段为Name,Address，要求得到这两个字段唯一的结果集select identity(int,1,1) as autoID, * into #Tmp from tableNameselect min(autoID) as autoID into #Tmp2 from #Tmp group byName,autoIDselect * from #Tmp where autoID in(select autoID from #tmp2)最终一个select 即得到了Name，Address 不重复的结果集更改

7、数据库中表的所属用户的两个方法大家可能会常常遇到一个数据库备份复原到另外一台机器结果导致全部的表都不能翻开了， 缘由是建表的时候承受了当时的数据库用户=第四篇=如何查询数据库中的重复记录？比方说有个表中的数据是这样：a a a b b c查询出的结果是： 记录 数量a 3b 2c 1怎样写这个SQL 语句？select distinct(name),count(*) from tabname group by name;想出来了，这样就可以排序了。select a1,count(a1) as total from tablename group by a1 order by total des

8、cselect distinct(a1),count(a1) as total from tablename group by a1 order by total desc加个 distinct 更有效率select p.*, m.* from table1 p left join table2 m on p.item1=m.item2 where p.item3='#$#%$' order by p.item3 asc limit 10就类似这么写=第五篇=如何查找数据库中的重复记录? 能在Access 中用的方法select *from 表 A inner join (se

9、lect 字段 1,字段 2 from 表 group by 字段 1,字段 2 having Count(*)1) B on A.字段 1=B.字段 1 and A.字段 2=B.字段 2问题：依据其中几个字段推断重复，只保存一条记录，但是要显示全部字段，怎么查询，感谢！ 比方字段 1 字段 2 字段 3 字段 4 a b c 1a b c 1a b d 2 a b d 3b b d 2想得到的结果为a b c 1a b d 2或者 3 b b d 2说明，依据字段 1，2,3 组合不重复，字段 4 不考虑，得到了 3 个记录但是也要显示字段 4。方法一：可以用临时表的方法来解决：Curre

10、ntProject.Connection.Execute “drop table temptable“ CurrentProject.Connection.Execute “select * into temptable from 表 2 where 1=2“ CurrentProject.Connection.Execute “insert into temptable(字段 1,字段 2,字段 3) SELECT DISTINCT 表 2.字段 1, 表 2.字段 2, 表 2.字段 3 FROM 表 2;“CurrentProject.Connection.Execute “UPDATE

11、 temptable INNER JOIN表 2 ON (表 2.字段1 = temptable.字段1) AND (表2.字段2 = temptable.字段2) AND (表2.字段3 = temptable. 字段 3) SET temptable.字段 4 = 表 2.字段 4;“方法二：可以直接使用一个SELECT 查询筛选出需要的数据： 可以假定第四字段都选值最小的SELECT 1,2, 3, Min(4) AS Min4FROM 表 1GROUP BY 表 1.1, 表 1.2, 表 1.3;问题： 表 2id NAME r1 r21 1 w ee1 1 1 12321 2 12

12、3 1231 2 12 4341 2 123 1232 1 123 123ID 为数值，NAME 为字符。每条记录没有唯一标识。要求取得 ID 和 NAME 合并后不重复的记录，如有重复保存其中一条即可，但要显示全部记录。答复：SELECT a.*, (select top 1 r1 from表 2 as a1 where a1.id=a.id and a1.name=a.name) AS r1, (select top 1 r2 from 表 2 as a2 where a2.id=a.id and a2.name=a.name) AS r2FROM SELECT DISTINCT 表 2.

13、id, 表 2.NAME FROM 表 2. AS a;SELECT a.*, dlookup(“r1“,“表 2“,“id=“ & a.id & “ and name='“& a.name & “'“) AS r1, dlookup(“r2“,“表 2“,“id=“ & a.id & “ and name='“& a.name & “'“) AS r2FROM SELECT DISTINCT 表 2.id, 表 2.NAME FROM 表 2. AS a;留意，上述代码中由于没有唯一标识列，因此显示的 R1 R2 的先后次序无从确定，一般是按输入的先后挨次，但

14、是微软没有官方资料说明到底按哪个挨次，请网友留意。请留意，上述表2 为没有唯一标识字段，假设现在再建立一个自动编号字段“主键”则可以用以下代码SELECT a.ID, a.name, b.r1, b.r2, b.主键FROM (SELECT 表 2.id, 表 2.NAME, Min(表 2.主键) AS 主键FROM 表 2GROUP BY 表 2.id, 表 2.NAME) AS a inner JOIN 表 2 AS b ON a.主键=b.主键;=第六篇=1.查询数据库中重复的记录:select realname,count(*) from users group by realnam

15、e having count(*)1=第七篇=SELECT T0.ItemCode, T0.ItemName FROM OITM T0 WHERE exists (select 1 from OITM A where A.CODEBARS = TO.CODEBARS And A.ItemCode TO.ItemCode)=第八篇=信任很多人在查询数据库时都会遇到检索某表中不重复记录的时候,提到检索不重复记录, 马上想到确实定是Distinct 或者Group By 分组,小弟在初次使用的时候遇到了一些麻烦,这里拿出来与大家共享,期望对更多的朋友有所帮助!先看看数据库表构造：表名: TEST 字

16、段: Id,A,B,C,D其中 B 字段包含重复值;Id A BC D 111 a34 bvb 222 a35 fgfg 333 d ht sdf 444 a345 de 555 csfsf sscv 666 b rt fg下面我们来看看用什么样的SQL 语句检索出不含重复记录的数据:使用 Distinct 关键字Distinct 关键字主要用来在SELECT 查询记录中依据某指定字段的值去除重复记录SELECT DISTINCT 字段名 FROM 表名 WHERE 检索条件字句所以用这样一句SQL 就可以去掉重复项了:color=SELECT DISTINCT (B) FROM TEST但是

17、:这里有一个格外格外需要留意的地方：SELECT DISTINCT 字段名后面不能再跟其他的字段，否则检索出来的记录仍旧会含有重复项；错误写法：SELECT DISTINCT 字段名 ，其他字段名 FROM 表名 WHERE 检索条件字句实际上,我们上面SQL 语句结果集里就只有B 字段;(一般状况下,这种结果应当是很难满足需求的)假设我们的记录集里还需要有其他字段值,那怎么办呢?实际上,我们完全可以用另一种方法来解决问题;只是需要用到子查询而已!使用 GROUP BY 分组有一点需要留意:使用带有GROUP BY 字句的查询语句时,在SELECT 列表指定的列要么是GROUP BY 指定的列

18、, 要么包含聚合组函数所以用这样一句SQL 就可以去掉重复项了:color=SELECT * FROM TEST WHERE id in (SELECT MIN(id) FROM TEST GROUP BY B)这样就得到我们想要的结果集了: IdA BC D 111 a34 bvb 333 d ht sdf 555 csfsf sscv 666 b rt fg=第九篇=mysql=我的 mysql 表中的帐号是 8 位的随机数,我现在想查帐号有没有重复的,应当怎样操作,select count(*) as num,帐号 from TABLE GROUP BY 帐号num 1 就有重复!=第十

19、篇=焦急的人直接看红字=在使用mysql 时，有时需要查询出某个字段不重复的记录，虽然mysql 供给有distinct 这个关键字来过滤掉多余的重复记录只保存一条，但往往只用它来返回不重复记录的条数，而不是用它来返回不重记录的全部值。其缘由是distinct 只能返回它的目标字段，而无法返回其它字段，这个问题让我困扰了很久，用distinct 不能解决的话，我只有用二重循环查询来解决，而这样对于一个数据量格外大的站来说，无疑是会直接影响到效率的。所以我花了很多时间来争论这个问题，网上也查不到解决方案，期间把容容拉来帮助，结果是我们两人都郁闷了。下面先来看看例子：table id name1

20、a2 b3 c4 c5 b库构造或许这样，这只是一个简洁的例子，实际状况会简单得多。比方我想用一条语句查询得到name 不重复的全部数据，那就必需使用 distinct 去掉多余的重复记录。select distinct name from table得到的结果是:name ab c似乎到达效果了，可是，我想要得到的是id 值呢？改一下查询语句吧:select distinct name, id from table结果会是:id name1 a2 b3 c4 c5 bdistinct 怎么没起作用？作用是起了的，不过他同时作用了两个字段，也就是必需得id 与name 都一样的才会被排解。我们

21、再改改查询语句:select id, distinct name from table很圆满，除了错误信息你什么也得不到，distinct 必需放在开头。难到不能把distinct 放到 where 条件里？能，照样报错。很麻烦吧？确实，费尽心思都没能解决这个问题。没方法，连续找人问。拉住公司里一JAVA 程序员，他给我演示了oracle 里使用distinct 之后，也没找到mysql里的解决方案，最终下班之前他建议我试试group by。试了半天，也不行，最终在mysql 手册里找到一个用法，用group_concat(distinct name)协作 group by name 实现了我

22、所需要的功能，兴奋，天佑我也，赶快试试。报错。郁闷。连mysql 手册也跟我过不去，先给了我期望，然后又把我推向无望，好狠哪。再认真一查，group_concat 函数是 4.1 支持，晕，我 4.0 的。没方法，升级，升完级一试， 成功。最终搞定了，不过这样一来，又必需要求客户也升级了。突然灵机一闪，既然可以使用group_concat 函数，那其它函数能行吗？抓紧用count 函数一试，成功，我。想哭啊，费了这么多工夫。原来就这么简洁。现在将完整语句放出:select *, count(distinct name) from table group by name结果:id name co

23、unt(distinct name) 1 a 12 b 13 c 1最终一项为哪一项多余的，不用管就行了，目的到达。唉，原来 mysql 这么笨，轻轻一下就把他骗过去了，郁闷也就我吧对了，还有容容那家伙，现在拿出来期望大家不要被这问题折腾。哦，对，再顺便说一句，group by 必需放在 order by 和 limit 之前，不然会报错，差不多了，发给容容放网站上去，我连续劳碌。更郁闷的事情发生了，在预备提交时容容觉察，有更简洁的解决方法。select id, name from table group by name select * from table group by name=第十

24、一篇=查询及删除重复记录的方法(一)1、查找表中多余的重复记录，重复记录是依据单个字段peopleId来推断select * from peoplewhere peopleId in (select peopleId from people group by peopleId having count(peopleId) 1)2、删除表中多余的重复记录，重复记录是依据单个字段peopleId来推断，只留有 rowid最小的记录delete from peoplewhere peopleId in (select peopleId from people group by peopleId ha

25、ving count(peopleId) 1)and rowid not in (select min(rowid) from people group by peopleId having count(peopleId )1)3、查找表中多余的重复记录多个字段 select * from vitae awhere (a.peopleId,a.seq) in (select peopleId,seq from vitae group by peopleId,seq having count(*) 1)4、删除表中多余的重复记录多个字段，只留有rowid 最小的记录delete from vit

26、ae awhere (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 awhere (a.peopleId,a.seq) in (select peopleId,seq from vit

27、ae 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,

28、Count(*) From A Group By Name,sex Having Count(*) 1(三)方法一declare max integer,id integerdeclare cur_rows cursor local for select主字段,count(*) from 表名 group by 主字段 having count(*) ； 1open cur_rowsfetch cur_rows into id,maxwhile fetch_status=0beginselect max = max -1set rowcount maxdelete from 表名 where

29、主字段 = idfetch cur_rows into id,maxendclose cur_rowsset rowcount 0方法二有两个意义上的重复记录，一是完全重复的记录，也即全部字段均重复的记录，二是局部关键字段重复的记录，比方Name 字段重复，而其他字段不肯定重复或都重复可以无视。1、对于第一种重复，比较简洁解决，使用select distinct * from tableName就可以得到无重复记录的结果集。假设该表需要删除重复的记录重复记录保存1 条，可以按以下方法删除select distinct * into #Tmp from tableNamedrop table t

30、ableNameselect * into tableName from #Tmpdrop table #Tmp发生这种重复的缘由是表设计不周产生的，增加唯一索引列即可解决。2、这类重复问题通常要求保存重复记录中的第一条记录，操作方法如下假设有重复的字段为Name,Address，要求得到这两个字段唯一的结果集select identity(int,1,1) as autoID, * into #Tmp from tableNameselect min(autoID) as autoID into #Tmp2 from #Tmp group by Name,autoIDselect * from #Tmp where autoID in(select autoID from #tmp2)最终一个select 即得到了Name，Address 不重复的结果集但多了一个autoID 字段， 实际写时可以写在select 子句中省去此列(四)查询重复select * from tablename where id in (select id from tablenamegroup by idhaving count(id) 1)