Oracle查询表里的重复数据方法

咱俩平时在数据库中有再次的记录当时大家盼望删除那二个重复的笔录
你不要告诉本人你是一条条手动删除的哈:
selectdistinct*intonewtableformtablename droptabletablename
select*intotablefromnewtable droptablenewtable 思路好了就好做.

一、背景

一张person表,有id和name的四个字段,id是独一的不容许再一次,id相仿则感觉是双重的记录。

二、解决

select id from group by id having count(*) 1

依照id分组并计数,有些id号那一组的数码超越1条则感觉重新。

哪些查询重复的数据

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

PS:将地点的号改为=号就能够查询出从未重新的数额了。

Oracle删除重复数据的SQL:

去除重复数据的宗旨布局写法:

想要删除那一个再度的多寡,能够行使上面语句举行删减

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

地方的SQL注意:语句极度容易,就是将查询到的多少删除掉。不过这种删除试行的效能十分低,对于大数据量来讲,大概会将数据库吊死。

建议先将查询到的再一次的多寡插入到二个一时表中,然后对进展删减,那样,实行删除的时候就毫无再进行一遍询问了。如下:

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删除重复数据的SQL:

oracle中,有个藏匿了机关rowid,里面给每条记下三个独一的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 )

括号中的SQL查询出rowid最大的记录,而外面便是查询出除了rowid最大之外的其余重复的多少了。

通过,大家要刨除重复数据,只保留最新的一条数据,就能够那样写了:

去除重复数据

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

删除重复数据

delete tab t where t.rowid  ( select min(t2.rowid) from tab t2 where t.col2 = t2.col2 and t.col8 = t2.col8)

本来,上边语句的实行功效是比十分的低的,能够虚构构建有的时候表,讲须求推断重复的字段、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 表名); truncate table 正式表; insert into 正式表 (select * from 临时表);drop table 临时表;

要是想删除八个表的重复数据,可以先建三个有的时候表,将去掉重复数据后的数额导入到有的时候表,然后在从一时表将数据导入正式表中,如下:

INSERT INTO t_table_bakselect distinct * from t_table;

MySQL查询及删除重复记录的方法查询及删除重复记录的点子

1、查找表中多余的重复记录,重复记录是依赖单个字段来决断

select * from table where tableId in (select tableId from table group by tableId having count(tableId)  1)

2、删除表中多余的重复记录,重复记录是基于单个字段来推断,只留有rowid最小的笔录。

delete from table where tableId in (select tableId from table group by tableId having count(tableId)  1) and rowid not in (select min(rowid) from table group by tableId having count(tableId )1)

3、查找表中剩下的重复记录

select * from vitae a where (a.tableId,a.seq) in (select tableId,seq from vitae group by tableId,seq having count(*)  1)

4、删除表中多余的重复记录,只留有rowid最小的记录

delete from vitae a where (a.tableId,a.seq) in (select tableId,seq from vitae group by tableId,seq having count(*)  1)and rowid not in (select min(rowid) from vitae group by tableId,seq having count(*)1)

5、查找表中多余的重复记录,不饱含rowid最小的记录

select * from vitae a where (a.tableId,a.seq) in (select tableId,seq from vitae group by tableId,seq having count(*)  1) and rowid not in (select min(rowid) from vitae group by tableId,seq having count(*)1)

6、查询出二个表中,某一列的值是相通的SQL:

比方说

在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

oracle查询表中字段里多少是不是有双重

查单个字段:

SELECT TEST_NAME,COUNT(*) FROM T_TEST GROUP BY TEST_NAME HAVING COUNT(*)  1

Oracle查询重复数据并删除,只保留一条记录1、查找表中多余的重复记录,重复记录是基于单个字段来推断

select * from 表 where Id in (select Id from 表 group byId having count(Id)  1)

2、删除表中多余的重复记录,重复记录是依照单个字段来判断,只留有rowid最小的笔录

DELETE from 表 WHERE (id) IN ( SELECT id FROM 表 GROUP BY id HAVING COUNT(id)  1) AND ROWID NOT IN (SELECT MIN(ROWID) FROM 表 GROUP BY id HAVING COUNT(*)  1);

3、查找表中剩下的重复记录

select * from 表 a where (a.Id,a.seq) in(select Id,seq from 表 group by Id,seq having count(*)  1)

4、删除表中多余的重复记录,只留有rowid最小的记录

delete from 表 a where (a.Id,a.seq) in (select Id,seq from 表 group by Id,seq having count(*)  1) and rowid not in (select min(rowid) from 表 group by Id,seq having count(*)1)

5、查找表中多余的重复记录,不含有rowid最小的记录

select * from 表 a where (a.Id,a.seq) in (select Id,seq from 表 group by Id,seq having count(*)  1) and rowid not in (select min(rowid) from 表 group by Id,seq having count(*)1)

诸如将来有壹职员表
若想将姓名、居民身份证号、住址那四个字段完全相仿的笔录查询出来

select p1.*from persons p1,persons p2where p1.idp2.idand 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 !=from table1 b where a.name1=b.name1 and a.name2=b.name2……)

删数据:

delete from table1 a where rowid !=from table1 b where a.name1=b.name1 and a.name2=b.name2……)

2.group by形式查数据:select count, max from student
–列出重新的记录数,并列出她的name属性group by numhaving count 1
–按num分组后寻找表中num列重复,即出现次数超越一回删数码:

delete from studentgroup by numhaving count 1

那样的话就把具备重复的都剔除了。3.用distinct方法 -对于小的表比较有用

create table table_new as select distinct * from table1 minuxtruncate table table1;insert into table1 select * from table_new;

查询及删除重复记录的诀要大全1、查找表中多余的重复记录,重复记录是依附单个字段来决断

select * from peoplewhere peopleId in  1)

2、删除表中多余的重复记录,重复记录是依照单个字段来剖断,只留有rowid最小的笔录

delete from peoplewhere peopleId in  1)and rowid not in from people group by peopleId having count1)

3、查找表中剩下的重复记录

select * from vitae awhere in  1)

4、删除表中多余的重复记录,只留有rowid最小的记录

delete from vitae awhere in  1)and rowid not in from vitae group by peopleId,seq having count1)

5、查找表中多余的重复记录,不分包rowid最小的记录

select * from vitae awhere in  1)and rowid not in from vitae group by peopleId,seq having count1)

比方说在A表中留存贰个字段“name”,并且不一致记录之间的“name”值有相当的大可能率会适合,今后正是内需查询出在该表中的各记录之间,“name”值存在双重的项;

Select Name,Count From A Group By Name Having Count  1

举例还查性别也一律大则如下:

Select Name,***,Count From A Group By Name,*** 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 主字段 = @idfetch cur_rows into @id,@maxendclose cur_rowsset rowcount 0

情势二"重复记录"有八个意思上的重复记录,一是全然重复的笔录,也即全部字段均再度的记录,二是局地最首要字段重复的笔录,例如Name字段重复,而其它字段不自然再一次或都再一次能够忽略。1、对于第一种重复,比比较容易于杀绝,使用select
distinct * from
tableName就能够得到无重复记录的结果集。如若该表须求删除重复的笔录,能够按以下方法删除

select distinct * into #Tmp from tableNamedrop table tableNameselect * into tableName from #Tmpdrop table #Tmp

爆发这种重新的因由是表设计不周发生的,扩充独一索引列就可以减轻。2、那类重复难点普通必要保存重复记录中的第一条记下,操作方法如下如若有重复的字段为Name,Address,供给获取那五个字段独一的结果集

select identity as autoID, * into #Tmp from tableNameselect min as autoID into #Tmp2 from #Tmp group by Name,autoIDselect * from #Tmp where autoID in

末了叁个select即获取了Name,Address不重复的结果集查询重复

select * from tablename where id in  1)

发表评论

电子邮件地址不会被公开。 必填项已用*标注