1、添加普通索引
CREATEINDEX IDX_TEST_TABLE_CODE ON TEST_TABLE(CODE);2、添加唯一键
ALTERTABLE TEST_TABLE ADDCONSTRAINT UK_TEST_TABLE_CODE UNIQUE(CODE)ENABLE NOVALIDATE;3、查找重复记录
SELECT*FROM TABLE_NAME A WHERE ROWID NOTIN(SELECTMAX(ROWID)FROM TABLE_NAME D
WHERE A.COL1 = D.COL1 AND A.COL2 = D.COL2);4、删除重复记录
DELETEFROM TABLE_NAMEWHERE ROWID NOTIN(SELECTMAX(ROWID)FROM TABLE_NAME D groupby d.col1,d.col2);5、删除重复记录只保留一条
DELETEFROM people WHERE peopleid IN(SELECT peopleid FROM people GROUPBY peopleid HAVINGCOUNT(peopleid)>1)AND ROWID NOTIN(SELECTMIN(ROWID)FROM people GROUPBY peopleid HAVINGCOUNT(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
GROUPBY phone_id
HAVINGCOUNT(phone_id)>1);2、将白名单表中重复电话号码删除只保留一条
DELETEFROM aid.BILL_DISPLAY_WHITE_PHONE_LIST
WHERE phone_id IN(SELECT phone_id
FROM aid.BILL_DISPLAY_WHITE_PHONE_LIST
GROUPBY phone_id
HAVINGCOUNT(phone_id)>1)AND ROWID NOTIN(SELECTMIN(ROWID)FROM aid.BILL_DISPLAY_WHITE_PHONE_LIST
GROUPBY phone_id
HAVINGCOUNT(phone_id)>1);3、给已有数据的表添加主键
ALTERTABLE aid.BILL_DISPLAY_WHITE_PHONE_LIST
ADDCONSTRAINT PK_BILLDISPLAYWHITEPHONELIST PRIMARYKEY(phone_id);