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')