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

測試去除重複資料

相關文章