Oracle中删除重复数据

1.删除重复数据

-------1.1-----------------------------------------------
--1.首先按照STUDENT表中的NAME, STUNO进行分区,排序，利用ROW_NUMBER()字段进行条件筛选，筛选出即将要删除的数据   2.然后利用ROWID进行删除条件。

DELETE FROM STUDENT WHERE ROWID IN(
SELECT ROWID FROM 
(SELECT A.*, ROWID, ROW_NUMBER() OVER(PARTITION BY NAME, STUNO ORDER BY SCORE) AS RANK FROM STUDENT A) WHERE RANK > 1);

-------1.2--------保留一条数据------------------------------
--1.选取最小的ROWID 2.删除ROWID不是最小的ROWID的所有数据

DELETE FROM STUDENT WHERE ROWID NOT IN(
SELECT MIN(ROWID) FROM STUDENT GROUP BY NAME, STUNO );

-------1.3---------------------------------------------------

DELETE FROM STUDENT a WHERE ROWID > (
SELECT MIN(ROWID) FROM STUDENT b where a.name = b.name and a.stuno = b.stuno);


-- 查看表所占用的大小
SELECT T.SEGMENT_NAME, T.SEGMENT_TYPE, SUM(T.BYTES/1024/1024) “占用空间(M)” FROM DBA_SEGMENTS T 
WHERE T.SEGMENT_NAME = ‘FABCARRIERHISTORY GROUP BY OWNER, T.SEGMENT_NAME, T.SEGMENT_TYPE;’
-- 是否自动提交
查看：show auto; 或者  show autocommit;
设置：set autocommit on;