主键重复处理办法

一 主键重复处理步骤

/**查询键值重复**/
select key,count(1) from table 
group by key 
having count(1)>1


---重复主键的处理(删除主键-删除重复数据-添加主键)


1-----------删除主键-------------
ALTER TABLE BANCS_INVS_F
  DROP PRIMARY KEY ;
  
2----------根据rowid，rownum删除重复数据-------------
DELETE BANCS_INVS_F D WHERE D.ROWID IN(
SELECT S.ROWID FROM(
SELECT ROWID,ROW_NUMBER()OVER(PARTITION BY INVS_SOC_NO,AD04_MASTER_AC_NO,CD04_ACCT_TYPE,CD04_INT_CATE,AD04_SUB_AC ORDER BY AD04_MASTER_AC_NO) AS ORDER_KEY FROM BANCS_INVS_F
)S
WHERE ORDER_KEY >= 2
)


3-----------添加主键--------------------
ALTER TABLE BANCS_INVS_F
  ADD PRIMARY KEY (INVS_SOC_NO,AD04_MASTER_AC_NO,CD04_ACCT_TYPE,CD04_INT_CATE,AD04_SUB_AC);

二 删除重复记录的3种方法

2 3可处理数据完全一样的记录

1 delete from table a 

where exists(select null from table b where b.name=a.name and b.x>a.x)(根据列x不同)


2 delete from table a            
where exists(select null from table b where b.name=a.name and b.rowid>a.rowid)(同方法一只是用rowid替代x)
 
3 delete from table
whre rowid in
(
select rowid from (select rowid,row_number()over(partition  by  name  order by id) as seqno)
where seqno>1

);