清理oracle数据库下的数据重复，并保留其一

来源：互联网发布：java抽象类能被继承编辑：程序博客网时间：2024/05/29 03:48

select * from b2bx where
create_date in
（to_date('2017-12-11 08:22:16', 'yyyy-mm-dd hh24:mi:ss'),
to_date('2017-12-11 08:22:17', 'yyyy-mm-dd hh24:mi:ss'),
to_date('2017-12-11 08:22:18', 'yyyy-mm-dd hh24:mi:ss'),
to_date('2017-12-11 08:22:19', 'yyyy-mm-dd hh24:mi:ss'),
to_date('2017-12-11 08:22:20', 'yyyy-mm-dd hh24:mi:ss'),
to_date('2017-12-11 08:22:21', 'yyyy-mm-dd hh24:mi:ss'),
to_date('2017-12-11 08:22:22', 'yyyy-mm-dd hh24:mi:ss'),
to_date('2017-12-11 08:22:23', 'yyyy-mm-dd hh24:mi:ss'),
to_date('2017-12-11 08:32:55', 'yyyy-mm-dd hh24:mi:ss'),
to_date('2017-12-11 08:32:56', 'yyyy-mm-dd hh24:mi:ss'),
to_date('2017-12-11 08:32:57', 'yyyy-mm-dd hh24:mi:ss'),
to_date('2017-12-11 08:32:58', 'yyyy-mm-dd hh24:mi:ss'),
to_date('2017-12-11 08:32:59', 'yyyy-mm-dd hh24:mi:ss'),
to_date('2017-12-11 08:33:00', 'yyyy-mm-dd hh24:mi:ss')
）and create_user='admin'
-- 141 - 94 = ？刚好47 重复 3次 94/2 = ? 47 、、此时重复2次然后再次执行一次就剩余47条 OK

select cc.id from (
select a.rowid,a.* from b2bx a
where a.rowid =
(
select max(b.rowid) from b2bx b
where a.lotn = b.lotn and
create_date in
（to_date('2017-12-11 08:22:16', 'yyyy-mm-dd hh24:mi:ss'),
to_date('2017-12-11 08:22:17', 'yyyy-mm-dd hh24:mi:ss'),
to_date('2017-12-11 08:22:18', 'yyyy-mm-dd hh24:mi:ss'),
to_date('2017-12-11 08:22:19', 'yyyy-mm-dd hh24:mi:ss'),
to_date('2017-12-11 08:22:20', 'yyyy-mm-dd hh24:mi:ss'),
to_date('2017-12-11 08:22:21', 'yyyy-mm-dd hh24:mi:ss'),
to_date('2017-12-11 08:22:22', 'yyyy-mm-dd hh24:mi:ss'),
to_date('2017-12-11 08:22:23', 'yyyy-mm-dd hh24:mi:ss'),
to_date('2017-12-11 08:32:55', 'yyyy-mm-dd hh24:mi:ss'),
to_date('2017-12-11 08:32:56', 'yyyy-mm-dd hh24:mi:ss'),
to_date('2017-12-11 08:32:57', 'yyyy-mm-dd hh24:mi:ss'),
to_date('2017-12-11 08:32:58', 'yyyy-mm-dd hh24:mi:ss'),
to_date('2017-12-11 08:32:59', 'yyyy-mm-dd hh24:mi:ss'),
to_date('2017-12-11 08:33:00', 'yyyy-mm-dd hh24:mi:ss')
）and create_user='admin'
)
) cc

select count(*) from b2bx ; -- 2962982 临时删除47 怎么只有40条
select count(*) from b2bx -- 2962952 临时删除47 怎么只有40条

select count(*) from b2bx -- 2962952 临时删除47 怎么只有40条
-- 2962952 -2962905 = ? 47 == 刚好 47

delete from b2bx where id in (
select cc.id from (

select a.rowid,a.* from b2bx a
where a.rowid =
(
select max(b.rowid) from b2bx b
where a.lotn = b.lotn and
create_date in
（to_date('2017-12-11 08:22:16', 'yyyy-mm-dd hh24:mi:ss'),
to_date('2017-12-11 08:22:17', 'yyyy-mm-dd hh24:mi:ss'),
to_date('2017-12-11 08:22:18', 'yyyy-mm-dd hh24:mi:ss'),
to_date('2017-12-11 08:22:19', 'yyyy-mm-dd hh24:mi:ss'),
to_date('2017-12-11 08:22:20', 'yyyy-mm-dd hh24:mi:ss'),
to_date('2017-12-11 08:22:21', 'yyyy-mm-dd hh24:mi:ss'),
to_date('2017-12-11 08:22:22', 'yyyy-mm-dd hh24:mi:ss'),
to_date('2017-12-11 08:22:23', 'yyyy-mm-dd hh24:mi:ss'),
to_date('2017-12-11 08:32:55', 'yyyy-mm-dd hh24:mi:ss'),
to_date('2017-12-11 08:32:56', 'yyyy-mm-dd hh24:mi:ss'),
to_date('2017-12-11 08:32:57', 'yyyy-mm-dd hh24:mi:ss'),
to_date('2017-12-11 08:32:58', 'yyyy-mm-dd hh24:mi:ss'),
to_date('2017-12-11 08:32:59', 'yyyy-mm-dd hh24:mi:ss'),
to_date('2017-12-11 08:33:00', 'yyyy-mm-dd hh24:mi:ss')
）and create_user='admin'
)
) cc
)

阅读全文

0 0