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)

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

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 )

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
)

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 );
mit;

select distinct  from 表名

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

INSERT INTO t_table_bak
select distinct  from t_table;

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

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)

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

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)

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

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

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

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);

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

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)

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 p2
where p1.id<>p2.id
and p1.cardid = p2.cardid and p1.pname = p2.pname and 
p1.address = p2.address

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……）

delete from student
group by num
having count（num） >1

create table table_new as  select distinct   from table1 
minux
truncate table table1;
insert into table1 select  from table_new;

select  from people
where peopleId in （select peopleId from people group by 
peopleId having count（peopleId） > 1）

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）

select  from vitae a
where （a.peopleId,a.seq） in （select peopleId,seq from vitae 
group by peopleId,seq having count（） > 1）

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）

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

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）

select  from tablename where id in （
select id from tablename
group by id
having count（id） > 1
）