ORACLE删除重复数据,保留其中一条

方法一 

适合数据量大的情况,分批次提交删除记录

declare
--定义存储结构
type bbscomment_type is record
(
    psr_id T_DISP_EQUIPID_RELATION.psr_id%type,
    obj_id T_DISP_EQUIPID_RELATION.obj_id%type
    
);
bbscomment_record bbscomment_type;

--可供比较的变量
v_psr_id T_DISP_EQUIPID_RELATION.psr_id%type;
v_obj_id T_DISP_EQUIPID_RELATION.obj_id%type;

--其它变量
v_batch_size integer := 200;
v_counter integer := 0;

cursor cur_dupl is
    --取出所有有重复的记录
    select obj_id, psr_id
    from T_DISP_EQUIPID_RELATION
    where PSR_ID in(
        select PSR_ID
        from T_DISP_EQUIPID_RELATION
        group by PSR_ID
        having count(*) > 1) order by psr_id;
begin
    for bbscomment_record in cur_dupl loop
        if v_psr_id is null or (bbscomment_record.psr_id != v_psr_id) then
            --首次进入、换记录了，都重新赋值
            v_psr_id := bbscomment_record.psr_id;
        else
            --其它记录删除
            delete from T_DISP_EQUIPID_RELATION where obj_id = bbscomment_record.obj_id;
            v_counter := v_counter + 1;

            if mod(v_counter, v_batch_size) = 0 then
                --每多少条提交一次
                commit;
            end if;
        end if;
    end loop;

    if v_counter > 0 then
        --最后一次提交
        commit;
    end if;
    dbms_output.put_line(to_char(v_counter)||'条记录被删除！');
    exception
        when others then
            dbms_output.put_line('sqlerrm-->' ||sqlerrm);
        rollback;
end;

方法二

适合数据量少的情况

delete 
    from t_measure_load_rate_oms a
where 
(a.equip_id,a.date_time ) 
    in (select equip_id,date_time 
        from t_measure_load_rate_oms 
        group by equip_id,date_time 
        having count(*) > 1
        )
and rowid not in (
    select min(rowid) from t_measure_load_rate_oms group by equip_id,date_time 
    having count(*)>1
    );