步骤:1、找出重复的数据;
2、用row_number 分组;
3、删掉 rn 为1的;即重复的数据
delete from tb_charging_log c where c.session_id in (
select b.session_id from (
select
trunc(a.start_time, ‘mi’),
trunc(a.end_time, ‘mi’),
a.bus_job_no,
a.station_no,
row_number() over(partition by trunc(a.start_time, ‘mi’),
trunc(a.end_time, ‘mi’),
a.bus_job_no,
a.station_no order by trunc(a.ins_time, ‘mi’)) rn,
a.charger_value,
a.ins_time,
a.charger_no,
a.session_id
from tb_charging_log a where (trunc(a.start_time, ‘mi’),
trunc(a.end_time, ‘mi’),
a.bus_job_no,
– a.charger_value,
a.station_no)
in (
select trunc(t.start_time, ‘mi’),
trunc(t.end_time, ‘mi’),
t.bus_job_no,
– t.charger_value,
t.station_no
from tb_charging_log t
where trunc(t.start_time, ‘MM’) = trunc(add_months(sysdate,-2), ‘MM’) and t.charger_value>0
group by trunc(t.start_time, ‘mi’),trunc(t.end_time, ‘mi’),t.bus_job_no,t.station_no having count(*)>1
)
) b where b.rn = 1
);