--直接这样就可以了,有几列关联几列,如果有主键列或唯一键列,就只关联主键或唯一键列就可以了 --没的话,就按所有列关联 delete 你的表 t1 where exists (select * from 你的表 t2 where t1.关联列=t2.关联列 and t1.rowid>t2.rowid);
--兩種數據保留方式 SQL> create table depttest as select * from dept; SQL> insert into depttest select * from dept where rownum<3; SQL> insert into depttest select * from dept where rownum<3; SQL> select * from depttest; DEPTNO DNAME LOC ---------- -------------- ------------- 10 ACCOUNTING NEW YORK 20 RESEARCH DALLAS 30 dname1 loc1 40 OPERATIONS BOSTON 10 ACCOUNTING NEW YORK 20 RESEARCH DALLAS 10 ACCOUNTING NEW YORK 20 RESEARCH DALLAS SQL> commit; SQL> --刪除重復記錄,但重復的保留一倏 SQL> delete depttest t1 where exists(select * from depttest t2 2 where t1.deptno=t2.deptno and t1.rowid>t2.rowid); SQL> select * from depttest; DEPTNO DNAME LOC ---------- -------------- ------------- 10 ACCOUNTING NEW YORK 20 RESEARCH DALLAS 30 dname1 loc1 40 OPERATIONS BOSTON SQL> rollback; SQL> --刪除重復記錄,重復記錄一倏都不保留 SQL> delete depttest t1 where exists(select * from depttest t2 2 where t1.deptno=t2.deptno and t1.rowid<>t2.rowid); SQL> select * from depttest; DEPTNO DNAME LOC ---------- -------------- ------------- 30 dname1 loc1 40 OPERATIONS BOSTON
根据rowid就可以删除了. 因为rowid是不变化,无论是保留几条,都可以根据ROWID来搞.SELECT A.*, A.ROWID FROM BAR A WHERE EXISTS (SELECT 1 FROM BAR B WHERE B.USERNAME = A.USERNAME AND B.USERAGE = A.USERAGE AND A.ROWID > B.ROWID)大致上就是这样子了
--直接这样就可以了,有几列关联几列,如果有主键列或唯一键列,就只关联主键或唯一键列就可以了
--没的话,就按所有列关联
delete 你的表 t1 where exists
(select * from 你的表 t2 where t1.关联列=t2.关联列 and t1.rowid>t2.rowid);
--兩種數據保留方式
SQL> create table depttest as select * from dept;
SQL> insert into depttest select * from dept where rownum<3;
SQL> insert into depttest select * from dept where rownum<3;
SQL> select * from depttest; DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 dname1 loc1
40 OPERATIONS BOSTON
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
SQL> commit;
SQL> --刪除重復記錄,但重復的保留一倏
SQL> delete depttest t1 where exists(select * from depttest t2
2 where t1.deptno=t2.deptno and t1.rowid>t2.rowid);
SQL> select * from depttest; DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 dname1 loc1
40 OPERATIONS BOSTON
SQL> rollback;
SQL> --刪除重復記錄,重復記錄一倏都不保留
SQL> delete depttest t1 where exists(select * from depttest t2
2 where t1.deptno=t2.deptno and t1.rowid<>t2.rowid);
SQL> select * from depttest; DEPTNO DNAME LOC
---------- -------------- -------------
30 dname1 loc1
40 OPERATIONS BOSTON
因为rowid是不变化,无论是保留几条,都可以根据ROWID来搞.SELECT A.*, A.ROWID
FROM BAR A
WHERE EXISTS (SELECT 1
FROM BAR B
WHERE B.USERNAME = A.USERNAME
AND B.USERAGE = A.USERAGE
AND A.ROWID > B.ROWID)大致上就是这样子了
上网搜索一下SQL去重,很多例子自己学习下吧
+++
可以先考虑惟一建
使用rowid