测试去除重复数据

1、创建测试表，初始化数据
JZH@jzh>create table t (x number,y number);
Table created.
JZH@jzh>insert into t values (1,1);
1 row created.
JZH@jzh>insert into t values (1,1);
1 row created.
JZH@jzh>insert into t values (2,2);
1 row created.
JZH@jzh>commit;
Commit complete.
JZH@jzh>select * from t;
         X          Y
---------- ----------
         1          1
         1          1
         2          2
2、使用distinct方法
JZH@jzh>select distinct x,y from t;
         X          Y
---------- ----------
         1          1
         2          2
3、使用group by方法
JZH@jzh>select x,y from t where (x,y) in (select x,y from t group by x,y having count(*)>1);
         X          Y
---------- ----------
         1          1
         1          1
4、使用rowid方法
1）查重
JZH@jzh>JZH@jzh>select * from t a where a.rowid <> (select max(rowid) from t b where a.x=b.x and a.y=b.y);
         X          Y
---------- ----------
         1          1
JZH@jzh>select * from t a where a.rowid <> (select min(rowid) from t b where a.x=b.x and a.y=b.y);
         X          Y
---------- ----------
         1          1
2）去重
JZH@jzh>delete from t a where a.rowid<>(select max(rowid) from t b where a.x=b.x and a.y=b.y);
1 row deleted.
JZH@jzh>commit;
Commit complete.
JZH@jzh>select * from t;
         X          Y
---------- ----------
         1          1
         2          2
5、使用分析函数方法
1)查重
JZH@jzh>select a.x,a.y,row_number () over (partition by a.x,a.y order by a.rowid) rn from t a;
         X          Y         RN
---------- ---------- ----------
         1          1          1
         1          1          2
         2          2          1
JZH@jzh>select b.x,b.y from (select a.x,a.y,row_number () over (partition by a.x,a.y order by a.rowid) rn from t a) b where b.rn>1;
         X          Y
---------- ----------
         1          1
2)去重
JZH@jzh>delete from t where rowid in (select rowid from (select a.x,a.y,row_number () over (partition by a.x,a.y order by a.rowid) rn from t a) b where rn>1);
1 row deleted.
JZH@jzh>select * from t;
         X          Y
---------- ----------
         1          1
         2          2

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/10271187/viewspace-1719676/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/10271187/viewspace-1719676/