--删除rn不等于1的rowid所在行的记录
delete from fhx_db_time t2 where t2.rowid in
(
select t1.rid from
(
--使用row_number() over(partition by XX order by XX)函数对重复数据加标识
select fdt.*,row_number()over (partition by dbuid ,end_interval_time order by dbuid) rn ,rowid rid
from fhx_db_time fdt
where fdt.insert_time between
to_date('2017-03-30 00:00:00','yyyy-mm-dd hh24:mi:ss') and
to_date('2017-03-31 00:00:00','yyyy-mm-dd hh24:mi:ss')
--将rn不等于1的数据剔除掉
) t1 where t1.rn<>1
)
delete from fhx_db_time t2 where t2.rowid in
(
select t1.rid from
(
--使用row_number() over(partition by XX order by XX)函数对重复数据加标识
select fdt.*,row_number()over (partition by dbuid ,end_interval_time order by dbuid) rn ,rowid rid
from fhx_db_time fdt
where fdt.insert_time between
to_date('2017-03-30 00:00:00','yyyy-mm-dd hh24:mi:ss') and
to_date('2017-03-31 00:00:00','yyyy-mm-dd hh24:mi:ss')
--将rn不等于1的数据剔除掉
) t1 where t1.rn<>1
)
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/29802484/viewspace-2136664/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/29802484/viewspace-2136664/