group by + having count(1)>1or 使用row_number over(partition by 字段) 删除大于2的就可以了。
delete from table where rowid not in (select max(rowid) from table group by field1,field2,... )
这样做可以,但是我觉得还是用min比较好,即保留一组里面的第一条记录,后续重复的都删掉!
delete from table a where a.id <(select b.id from table b where a.id = b.id)
方法一 先备份表,删除了重新插于 create table test as (select distinct * from test1) truncate table test insert into test select * from test1; drop table test1 方法二 delete from test where rowid not in (select max(t1.rowid) from test1 t1 group by id,city,num)
emp_name VARCHAR2(20) salary NUMBER(10,2)
可以通过下面的语句查询重复的记录:
SQL> select * from employee; EMP_ID EMP_NAME SALARY 1 sunshine 10000 1 sunshine 10000 2 semon 20000 2 semon 20000 3 xyz 30000 2 semon 20000 SQL> select distinct * from employee; EMP_ID EMP_NAME SALARY 1 sunshine 10000 2 semon 20000 3 xyz 30000 SQL> select * from employee group by emp_id,emp_name,salary having count (*)>1 EMP_ID EMP_NAME SALARY 1 sunshine 10000 2 semon 20000 SQL> select * from employee e1 where rowid in (select max(rowid) from employe e2
where e1.emp_id=e2.emp_id and e1.emp_name=e2.emp_name and e1.salary=e2.salary);
EMP_ID EMP_NAME SALARY 1 sunshine 10000 3 xyz 30000 2 semon 20000
2. 删除的几种方法: (1)通过建立临时表来实现 SQL>create table temp_emp as (select distinct * from employee) SQL> truncate table employee; (清空employee表的数据) SQL> insert into employee select * from temp_emp; (再将临时表里的内容插回来)
( 2)通过唯一rowid实现删除重复记录.在Oracle中,每一条记录都有一个rowid,rowid在整个数据库中是唯一的,rowid确定了每条记录是在Oracle中的哪一个数据文件、块、行上。在重复的记录中,可能所有列的内容都相同,但rowid不会相同,所以只要确定出重复记录中那些具有最大或最小rowid的就可以了,其余全部删除。 SQL>delete from employee e2 where rowid not in (
select max(e1.rowid) from employee e1 where e1.emp_id=e2.emp_id and e1.emp_name=e2.emp_name and e1.salary=e2.salary);--这里用min(rowid)也可以。 SQL>delete from employee e2 where rowid <(
select max(e1.rowid) from employee e1 where
e1.emp_id=e2.emp_id and e1.emp_name=e2.emp_name and e1.salary=e2.salary); (3)也是通过rowid,但效率更高。
SQL>delete from employee where rowid not in (
select max(t1.rowid) from employee t1 group by t1.emp_id,t1.emp_name,t1.salary);--这里用min(rowid)也可以。
EMP_ID EMP_NAME SALARY 1 sunshine 10000 3 xyz 30000 2 semon 20000
删除大于2的就可以了。
http://space.itpub.net/10768286/viewspace-83463
僅供參考啊!
删除大于2的就可以了。
先备份表,删除了重新插于
create table test as (select distinct * from test1)
truncate table test
insert into test select * from test1; drop table test1 方法二
delete from test where rowid not in (select max(t1.rowid) from test1 t1 group by id,city,num)