Oracle删除表中重复记录，只保留一条数据

1、已有重复数据的表中添加主键并且删除重复数据只保留一条

1、添加普通索引
CREATE INDEX IDX_TEST_TABLE_CODE ON TEST_TABLE(CODE);

2、添加唯一键
ALTER TABLE TEST_TABLE ADD CONSTRAINT UK_TEST_TABLE_CODE UNIQUE(CODE) ENABLE NOVALIDATE;

3、查找重复记录
SELECT * FROM TABLE_NAME A WHERE ROWID NOT IN 
(SELECT MAX(ROWID) FROM TABLE_NAME D 
WHERE A.COL1 = D.COL1 AND A.COL2 = D.COL2);

 4、删除重复记录 
DELETE FROM TABLE_NAMEWHERE ROWID NOT IN (SELECT MAX(ROWID) FROM TABLE_NAME D group by d.col1,d.col2);


5、删除重复记录只保留一条
DELETE FROM	people WHERE	peopleid IN (	SELECT	peopleid	FROM	people	GROUP BY	peopleid	HAVING COUNT (peopleid) > 1	) AND ROWID NOT IN ( SELECT MIN (ROWID)	FROM people GROUP BY peopleid HAVING COUNT (peopleid) > 1 )

2、实践

1、查找白名单中重复数据
SELECT *
FROM aid.BILL_DISPLAY_WHITE_PHONE_LIST
WHERE phone_id IN (
	SELECT phone_id
	FROM aid.BILL_DISPLAY_WHITE_PHONE_LIST
	GROUP BY phone_id
	HAVING COUNT(phone_id) > 1
);
2、将白名单表中重复电话号码删除只保留一条

DELETE FROM aid.BILL_DISPLAY_WHITE_PHONE_LIST
WHERE phone_id IN (
		SELECT phone_id
		FROM aid.BILL_DISPLAY_WHITE_PHONE_LIST
		GROUP BY phone_id
		HAVING COUNT(phone_id) > 1
	)
	AND ROWID NOT IN (
		SELECT MIN(ROWID)
		FROM aid.BILL_DISPLAY_WHITE_PHONE_LIST
		GROUP BY phone_id
		HAVING COUNT(phone_id) > 1
	);
    
 3、给已有数据的表添加主键
ALTER TABLE aid.BILL_DISPLAY_WHITE_PHONE_LIST
	ADD CONSTRAINT PK_BILLDISPLAYWHITEPHONELIST PRIMARY KEY (phone_id);