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

with
t1 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_TIME
3 1 jack 20120601 23:01:00
5 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……