delete后回滚数据，删除重复数据

查询某个时间段内的原始数据

SELECT * FROM table AS OF TIMESTAMP TO_TIMESTAMP('2016-05-10 08:55:17', ‘YYYY-MM-DD HH24:MI:SS)

 

1、查找表中多余的重复记录，重复记录是根据单个字段（Id）来判断

select * from jygl_qyk where zgdw in (select zgdw from jygl_qyk group by zgdw having count(zgdw) > 1);

2、删除表中多余的重复记录，重复记录是根据单个字段（Id）来判断，只留有rowid最小的记录

DELETE from jygl_qyk WHERE (zgdw) IN ( SELECT zgdw FROM jygl_qyk GROUP BY zgdw HAVING COUNT(zgdw) > 1)
 AND ROWID NOT IN (SELECT MIN(ROWID) FROM jygl_qyk GROUP BY zgdw HAVING COUNT(*) > 1);

 

3、查找表中多余的重复记录（多个字段）

select * from jygl_qyk a where (a.zgdw,a.jtjsdw,a.bddz) in(select zgdw,jtjsdw,bddz from jygl_qyk
 group by zgdw,jtjsdw,bddz having count(*) > 1)
 4、删除表中多余的重复记录（多个字段），只留有rowid最小的记录
 delete from jygl_qyk a where (a.zgdw,a.jtjsdw,a.bddz) in (select zgdw,jtjsdw,bddz from jygl_qyk
  group by zgdw,jtjsdw,bddz having count(*) > 1)
 and rowid not in (select min(rowid) from jygl_qyk group by zgdw,jtjsdw,bddz having count(*)>1)

 

5、查询某个时间段的原始数据

select * from sfgl_yjfy
        as of timestamp to_timestamp('2016-09-27 18:00:00','yyyy-mm-dd,hh24:mi:ss')