删除表中的重复数据

–1.重复数据整理，保留最大的rowid的数据
create table SL_HR_PERSON_PAY_del as (

SELECT t.Pk_Wadata,max(rowid) dataid ,count(1) num1 FROM SL_HR_PERSON_PAY t group by t.Pk_Wadata having count(1)>1);
–2.插入重复数据中的 rowid最大的数据
create table SL_HR_PERSON_PAY_insert as (
SELECT t.* FROM SL_HR_PERSON_PAY t , SL_HR_PERSON_PAY_del td where td.Pk_Wadata=t.Pk_Wadata and t.rowid=td.dataid);
–3.找到不重复的数据的rowid
create table SL_HR_PERSON_PAY_baoliu as (

SELECT t.Pk_Wadata,max(rowid) dataid ,count(1) num1 FROM SL_HR_PERSON_PAY t group by t.Pk_Wadata having count(1)=1);
–4.插入不重复的数据
insert into SL_HR_PERSON_PAY_insert (
SELECT t.* FROM SL_HR_PERSON_PAY t , SL_HR_PERSON_PAY_baoliu td where td.Pk_Wadata=t.Pk_Wadata and t.rowid=td.dataid);
–5.删除数据
truncate table SL_HR_PERSON_PAY;
–6.重新插入数据
insert into SL_HR_PERSON_PAY (SELECT t.* FROM SL_HR_PERSON_PAY_insert t);
commit;
drop table SL_HR_PERSON_PAY_del;
drop table SL_HR_PERSON_PAY_insert;
drop table SL_HR_PERSON_PAY_baoliu;