--创建历史表将重复数据的rowid得到 create table1 as select rid,dh from(select t.rowid as rid,dh,row_number()over(partition by dh order by 1) rn from table_name t) where rn>1;--删除 delete from table_name where rowid in(select rid from table1);
partition by dh :以DH来分组,DH相同的就被视为一组,在这里可以被视为一条数据; row_number():就是一个生成顺序号的函数 你可以先执行select t.rowid as rid,dh,row_number()over(partition by dh order by 1) rn from table_name t,试试看结果
DELETE FROM TABLE E WHERE E.ROWID > (SELECT MIN(X.ROWID) FROM TABLE X WHERE X.DH = E.DH);
delete from table_name a where rowid not in ( select rowid from table_name b group by b.dh )
where rowid not in (select MAX(rowid) from TABLE group by DH)
where rowid
in(select rowid,count(*) from table1 group by rowid having count(*)>1
where rowid
in(select rowid,count(*) from table1 group by rowid having count(*)>1)
我的表名是table_all_0829
谢谢大家
row_number():就是一个生成顺序号的函数
你可以先执行select t.rowid as rid,dh,row_number()over(partition by dh order by 1) rn
from table_name t,试试看结果
WHERE E.ROWID > (SELECT MIN(X.ROWID) FROM TABLE X WHERE X.DH = E.DH);
delete from table_name a where rowid not in ( select rowid from table_name b group by b.dh )
如果是其他目的的话,那就当我没说