当前位置: 代码迷 >> Oracle开发 >> Oracle 删除重复数据(只留最新一条) 不管你行不行都过来看看解决办法
  详细解决方案

Oracle 删除重复数据(只留最新一条) 不管你行不行都过来看看解决办法

热度:25   发布时间:2016-04-24 07:27:48.0
Oracle 删除重复数据(只留最新一条) 不管你行不行都过来看看
不多说

表:table1{id,app_id,create_user,create_time}

app_id 和 create_user 相同的为重复数据 (即两条数据,app_id相等,并且cureate_user相等,那么就是有重复数据)

要留 create_time 最大(最新)的数据,删除其他重复的。




上语句 满分, 带解释的,加分

------解决方案--------------------
代码不是太长,看一下吧。
不理解,再问我。呵呵

declare
v_appid varchar2(100) := "";
cursor mycur is
select id,app_id,create_user,create_time from table1
order by app_id,create_time desc;
r_mycur table1%rowtype;

begin

open mycur;
loop
fetch mycur into r_mycur;
exit when mycur%notfound;

if(v_appid = r_mycur.app_id) then
delete table1
where app_id = r_mycur.app_id
and create_time = r_mycur.app_id;
end if;
v_appid := r_mycur.app_id;

end loop;
close mycur;

commit;

end;
------解决方案--------------------
SQL code
create table table1(id int,app_id int,create_user varchar2(10),created_time date);
------解决方案--------------------
SQL code
delete from table1 where id in (select id                from (select t.id,                             t.create_time,                             max(create_time) over(partition by app_id, create_user) maxtime,--按app_id, create_user分组取最大时间                             count(1) over(partition by app_id, create_user) grpcnt --计算按app_id, create_user分组时每组中的记录条数                        from table1 t                       order by id) t               where t.grpcnt > 1                 and t.create_time = t.maxtime)
------解决方案--------------------
SQL code
witht1 as (select id,app_id,count(*) cnt from table1 group by id,app_id having count(*)>1), --查找重复记录t2 as (select id,app_id,max(create_time) c_time from table1 group by id,app_id), --查找最大时间t3 as (select t1.id,t1.app_id,t2.c_time from t1,t2 where t1.id=t2.id and t1.app_id=t2.app_id), --关联重复记录的最大时间delete from table1 a where exists (select 1 from t3 where a.id=t3.id and a.app_id=t3.app_id and a.create_time<>t3.c_time) --删除不是最大时间的记录
------解决方案--------------------
SQL code
CREATE TABLE test(id VARCHAR2(10),app_id VARCHAR2(10),create_user VARCHAR2(50),create_time VARCHAR2(30));INSERT INTO test VALUES ('1','1','jack','20120601 12:01:00');INSERT INTO test VALUES ('2','1','jack','20120601 12:50:00');INSERT INTO test VALUES ('3','1','jack','20120601 23:01:00');INSERT INTO test VALUES ('4','1','rose','20120615 12:01:00');INSERT INTO test VALUES ('5','1','rose','20120616 12:01:00');DELETE FROM test t WHERE NOT EXISTS   (SELECT 1 FROM      (SELECT DISTINCT app_id,create_user, Last_Value(create_time) over (PARTITION BY app_id,create_user ORDER BY create_time rows between unbounded preceding and unbounded following)create_time        FROM test     )   WHERE app_id=t.app_id AND create_user=t.create_user AND create_time=t.create_time  )SELECT * FROM test;ID, APP_ID, CREATE_USER, CREATE_TIME3 1 jack 20120601 23:01:005 1 rose 20120616 12:01:00
------解决方案--------------------
探讨

SQL code
CREATE TABLE test(id VARCHAR2(10),app_id VARCHAR2(10),create_user VARCHAR2(50),create_time VARCHAR2(30));
INSERT INTO test VALUES ('1','1','jack','20120601 12:01:00');
INSERT INTO test VALU……
  相关解决方案