现在有A B C 三张表,
A 表字段 a b c 主键 a 自增;
B 表字段 a d e 主键 a 根据 A 表生成 ;
C 表字段 f a g 主键 f 自增,a 根据 A 表生成 ;
每个表都有重复数据,如何删除这些重复数据呢?
A 表字段 a b c 主键 a 自增;
B 表字段 a d e 主键 a 根据 A 表生成 ;
C 表字段 f a g 主键 f 自增,a 根据 A 表生成 ;
每个表都有重复数据,如何删除这些重复数据呢?
DELETE B WHERE ROWID IN (SELECT MAX(ROWID) FROM B GROUP BY A,D,E);
DELETE A WHERE ROWID IN (SELECT MAX(ROWID) FROM A GROUP BY A,B,C);
COMMIT;
举个例子:Oracle
构建数据
DROP TABLE t purge ;
CREATE TABLE t AS SELECT * FROM dba_objects WHERE rownum<=10;
UPDATE t SET object_id=rownum;
UPDATE t SET object_id=3 WHERE object_id<=3;
UPDATE t SET object_id=4 WHERE object_id>=4 AND object_id<=6;
COMMIT;
第一种根据 max(rowid) 或者 min(rowid)
delete from t
where rowid <
(select max(rowid) from t t2
where t.object_id = t2.object_id
);
第二种:
(注意,这里的写法和上面的并不完全等价,上面是随便删除,保留rowid最大一条,
下面是用分析函数,保留时间最新的,上面既然可以随便删了,下面的写法肯定也是符合要求的。其中row_number必须要有order BY 关键字):
delete t
where rowid in (select rid
from (select rowid rid,
row_number() over(partition by object_id ORDER by created desc) rn
from t)
where rn > 1);
分解第二种方法:
SELECT object_id,rowid rid,
row_number() over(partition by object_id ORDER by created desc) rn
from t; --利用分析函数根据创建时间、object_id查询最新记录
SELECT object_id, rid,rn
FROM (select object_id,rowid rid,
row_number() over(partition by object_id ORDER by created desc) rn
from t)
where rn > 1;--查询重复数据的rowid
delete t
where rowid in (select rid
from (select rowid rid,
row_number() over(partition by object_id ORDER by created desc) rn
from t)
where rn > 1); --根据rowid删除。。
完毕。。