Oracle处理重复数据

在一张表中某个字段下面有重复记录，有很多方法，但是有一个方法，是比较"高效"的，如下语句：


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 data_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_number() over(PARTITION BY data_guid ORDER BY ROWID) m
                           FROM adam_entity_datas)
                  WHERE m <> 1)


目前只知道这三种比较有效的方法。


第一种方法比较好理解，但是最慢，第二种方法最快，但是选出来的记录是所有重复的记录，而不是一个重复记录的列表，第三种方法，我认为最好。




====================================================================================================================================


查询及删除重复记录的SQL语句


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)
注:rowid为oracle自带不用该.....


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)


(二)
比方说
在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
　　).


SELECT *
FROM t_info a
WHERE ((SELECT COUNT(*)
 FROM t_info
 WHERE Title = a.Title) > 1)
ORDER BY Title DESC




====================================================================================================================================






比如现在有一人员表 (表名：peosons)
若想将姓名、身份证号、住址这三个字段完全相同的记录查询出来


SELECT p1.*
  FROM persons p1,
       persons p2
 WHERE p1.id <> p2.id
   AND p1.cardid = p2.cardid
   AND p1.pname = p2.pname
   AND p1.address = p2.address


可以实现上述效果。
几个删除重复记录的SQL语句
　　1.用rowid方法
　　2.用group by方法
　　3.用distinct方法


1。用rowid方法


　　据据oracle带的rowid属性，进行判断，是否存在重复，语句如下：
　　查数据：
　　SELECT *
 FROM table1 a
WHERE ROWID != (SELECT MAX(ROWID)
  FROM table1 b
 WHERE a.name1 = b.name1
   AND a.name2 = b.name2……)


　　删数据：
    DELETE FROM table1 a
WHERE ROWID != (SELECT MAX(ROWID)
  FROM table1 b
 WHERE a.name1 = b.name1
   AND a.name2 = b.name2……)
　　
2.group by方法
　　查数据：
　　SELECT COUNT(num) ， MAX(NAME)
 FROM student --列出重复的记录数，并列出他的name属性
      　　group BY num 　　having COUNT(num) > 1 --按num分组后找出表中num列重复，即出现次数大于一次


　　删数据：
　　DELETE FROM student GROUP BY num HAVING COUNT(num) > 1


　　这样的话就把所有重复的都删除了。


3.用distinct方法 -对于小的表比较有用
　　create table table_new as select distinct * from table1 
    minux truncate table table1;
　　insert into table1 select * from table_new;




====================================================================================================================================






一。查找重复记录


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最大一条记录


====================================================================================================================================


Oracle：sql语句查询没有重复的记录数目
 1、通过创建临时表
可以把数据先导入到一个临时表中，然后删除原表的数据，再把数据导回原表，SQL语句如下：
creat table tbl_tmp (select distinct* from tbl); truncate table tbl;//清空表记录 insert into tbl select * from tbl_tmp; //将临时表中的数据插回来。
这种方法可以实现需求，但是很明显，对于一个千万级记录的表，这种方法很慢，在生产系统中，这会给系统带来很大的开销，不可行。
2、利用rowid
在oracle中，每一条记录都有一个rowid，rowid在整个数据库中是唯一的，rowid确定了每条记录是oracle中的哪一个数据文件、块、行上。在重复的记录中，可能所有列的内容都相同，但rowid不会相同。SQL语句如下：
delete from tbl where rowid in ( select a.rowid from tbl a, tbl b where a.rowid>b.rowid and a.col1=b.col1 and a.col2 = b.col2)
如果已经知道每条记录只有一条重复的，这个sql语句适用。但是如果每条记录的重复记录有N条，这个N是未知的，就要考虑适用下面这种方法了。
3、利用max或min函数
这里也要使用rowid，与上面不同的是结合max或min函数来实现。SQL语句如下
delete from tbl awhere rowid not in ( select max(b.rowid) from tbl b where a.col1=b.col1 and a.col2 = b.col2); //这里max使用min也可以 或者用下面的语句 delete from tbl awhere rowid<(select max(b.rowid) from tbl b where a.col1=b.col1 and a.col2 = b.col2); //这里如果把max换成min的话，前面的where子句中需要把"<"改为">"
跟上面的方法思路基本是一样的，不过使用了group by，减少了显性的比较条件，提高效率。SQL语句如下：
deletefrom tbl where rowid not in (select max(rowid) from tbl tgroup by t.col1, t.col2); delete from tbl where (col1, col2) in (select col1,col2 from tbl group bycol1,col2 havingcount(*) >1) and rowid not in (select nin(rowid) from tbl group by col1,col2 having count(*) >1)
还有一种方法，对于表中有重复记录的记录比较少的，并且有索引的情况，比较适用。假定col1，col2上有索引，并且tbl表中有重复记录的记录比较少，SQL语句如下4、利用group by，提高效率
4、Group By方法
一、查数据
Select count(Num) ,max(Name) from student –列出重复记录，并列出属性
Group By Name
Having count(Num) >1 –按Name分组后找出表中Num列重复，即出现次数大于1
二、删数据
Delete from student Group By Name Having count(Num) > 1
(一)
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)
select g.country,count(DISTINCT(g.GENE_ID)) as gene_count from (select p.gene_id,upper(t.country) as country
        from medline_citation t,tb_gene_2pubmed p
        where t.pmid=p.pubmed_id and t.country is not null ) g group by g.country








====================================================================================================================================




在ORACLE中如何删除表中的重复数据  
我们可能会出现这种情况，某个表原来设计不周全，导致表里面的数据数据重复，那么，如何对重复的数据进行删除呢？


重复的数据可能有这样两种情况，第一种时表中只有某些字段一样，第二种是两行记录完全一样。


一、对于部分字段重复数据的删除


先来谈谈如何查询重复的数据吧。


下面语句可以查询出那些数据是重复的：


select 字段1,字段2,count(*) from 表名 group by 字段1,字段2 having count(*) > 1


将上面的>号改为=号就可以查询出没有重复的数据了。


想要删除这些重复的数据，可以使用下面语句进行删除


delete from 表名 a where 字段1,字段2 in
(select 字段1,字段2,count(*) from 表名 group by 字段1,字段2 having count(*) > 1)


上面的语句非常简单，就是将查询到的数据删除掉。不过这种删除执行的效率非常低，对于大数据量来说，可能会将数据库吊死。
所以我建议先将查询到的重复的数据插入到一个临时表中，然后对进行删除，这样，执行删除的时候就不用再进行一次查询了。如下：


CREATE TABLE 临时表 AS
(select 字段1,字段2,count(*) from 表名 group by 字段1,字段2 having count(*) > 1)


上面这句话就是建立了临时表，并将查询到的数据插入其中。


下面就可以进行这样的删除操作了：


delete from 表名 a where 字段1,字段2 in (select 字段1，字段2 from 临时表);


这种先建临时表再进行删除的操作要比直接用一条语句进行删除要高效得多。


这个时候，大家可能会跳出来说，什么？你叫我们执行这种语句，那不是把所有重复的全都删除吗？
而我们想保留重复数据中最新的一条记录啊！大家不要急，下面我就讲一下如何进行这种操作。


在oracle中，有个隐藏了自动rowid，里面给每条记录一个唯一的rowid，我们如果想保留最新的一条记录，


我们就可以利用这个字段，保留重复数据中rowid最大的一条记录就可以了。


下面是查询重复数据的一个例子：


select a.rowid,a.* from 表名 a 
where a.rowid != 
(
select max(b.rowid) from 表名 b 
where a.字段1 = b.字段1 and 
a.字段2 = b.字段2 
)


下面我就来讲解一下，上面括号中的语句是查询出重复数据中rowid最大的一条记录。


而外面就是查询出除了rowid最大之外的其他重复的数据了。


由此，我们要删除重复数据，只保留最新的一条数据，就可以这样写了：


delete from 表名 a 
where a.rowid != 
(
select max(b.rowid) from 表名 b 
where a.字段1 = b.字段1 and 
a.字段2 = b.字段2 
)


随便说一下，上面语句的执行效率是很低的，可以考虑建立临时表，讲需要判断重复的字段、rowid插入临时表中，
然后删除的时候在进行比较。


create table 临时表 as 
select a.字段1,a.字段2,MAX(a.ROWID) dataid from 正式表 a GROUP BY a.字段1,a.字段2;
delete from 表名 a 
where a.rowid != 
(
select b.dataid from 临时表 b 
where a.字段1 = b.字段1 and 
a.字段2 = b.字段2 
);
commit;


二、对于完全重复记录的删除


对于表中两行记录完全一样的情况，可以用下面语句获取到去掉重复数据后的记录：


select distinct * from 表名


可以将查询的记录放到临时表中，然后再将原来的表记录删除，最后将临时表的数据导回原来的表中。如下：


CREATE TABLE 临时表 AS (select distinct * from 表名);
drop table 正式表;
insert into 正式表 (select * from 临时表);
drop table 临时表;


如果想删除一个表的重复数据，可以先建一个临时表，将去掉重复数据后的数据导入到临时表，
然后在从临时表将数据导入正式表中，如下：


INSERT INTO t_table_bak
select distinct * from t_table;