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 临时表);
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 临时表;
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)
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、查找表中多余的重复记录,重复记录是根据单个字段(Id)来判断
select
* from 表 where Id in (select Id from 表 group byId having count(Id) >
1)
2、删除表中多余的重复记录,重复记录是根据单个字段(Id)来判断,只留有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 * 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;
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 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 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
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)