-
-
yangm 数据达人Lv4
发表于2020-2-20 18:27
楼主
本帖最后由 yangm 于 2020-2-20 18:31 编辑
一 主键重复处理步骤
/**查询键值重复**/
select key,count(1) from table
group by key
having count(1)>1
---重复主键的处理(删除主键-删除重复数据-添加主键)
1-----------删除主键-------------
ALTER TABLE BANCS_INVS_FDROP PRIMARY KEY ;
2----------根据rowid，rownum删除重复数据-------------
DELETE BANCS_INVS_F D WHERE D.ROWID IN(
SELECT S.ROWID FROM(
SELECT ROWID,ROW_NUMBER()OVER(PARTITION BYINVS_SOC_NO,AD04_MASTER_AC_NO,CD04_ACCT_TYPE,CD04_INT_CATE,AD04_SUB_AC ORDER BYAD04_MASTER_AC_NO) AS ORDER_KEY FROM BANCS_INVS_F
)S
WHERE ORDER_KEY >= 2
）
3-----------添加主键--------------------
ALTER TABLE BANCS_INVS_FADD 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 whereb.name=a.name and b.x>a.x)(根据列x不同)
2 delete from table a where exists(select null from table b whereb.name=a.name and b.rowid>a.rowid)(同方法一只是用rowid替代x)
3 delete from table
whre rowid in
(
select rowid from (selectrowid,row_number()over(partition by name order by id) asseqno)
where seqno>1
);
