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

最新推荐文章于 2024-10-21 16:59:42 发布

原创最新推荐文章于 2024-10-21 16:59:42 发布 · 3.3k 阅读

3 ·

CC 4.0 BY-SA版权

文章标签：

#开发工具 #c#

Oracle 专栏收录该内容

11 篇文章

订阅专栏

本文详细介绍了如何在数据库中进行高效的数据查询，包括删除多余重复记录、查询特定时间段内的原始数据等关键操作。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

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

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