oracle 根据时间排序，删除相邻的除排序字段均相同的两条中较新的一条记录

问题描述：

  将表数据根据时间进行排序，若其中有相邻两条记录，除了时间字段外均相同，则删除两条中较新的一条记录(如下图)

解决方法：

1.建表并插入数据

create table t (id varchar2(10), name varchar2(20), sex varchar2(2), time date);
insert into t Select '001','aa','f',sysdate from dual;
insert into t Select '002','bb','f',sysdate+2 from dual;
insert into t Select '003','cc','m',sysdate+5 from dual;
insert into t Select '004','dd','m',sysdate+7 from dual;
insert into t Select '005','ee','m',sysdate+8 from dual;
insert into t Select '006','ff','f',sysdate+8 from dual;
insert into t Select '007','ff','f',sysdate+9 from dual;
insert into t Select '006','ff','f',sysdate+9 from dual;
insert into t Select '003','cc','m',sysdate+2 from dual;
insert into t Select '003','cc','m',sysdate+10 from dual;
insert into t Select '003','cc','m',sysdate+11 from dual;
insert into t Select '002','bb','f',sysdate+11 from dual;

2.查询要被删除的数据

方法一：

SQL> Select t1.id, t1.name,t1.sex, t1.time
  2  From
  3  (
  4      select id ,name,sex, time ,row_number() over(order by time) seq1  from t order by time
  5  ) t1
  6   inner join
  7  (
  8      select id ,name,sex, time ,row_number() over(order by time) seq2  from t order by time
  9  ) t2
 10  On t1.seq1=t2.seq2+1 and t1.id=t2.id and t1.name=t2.name and t1.sex=t2.sex;

ID         NAME                 SE TIME
---------- -------------------- -- --------------
006        ff                   f  13-7月 -17
003        cc                   m  15-7月 -17

方法二：

SQL> SELECT id, name, sex, time
  2  FROM
  3  (
  4       SELECT A.ID,
  5           A.NAME,
  6           A.SEX,
  7           A.TIME,
  8           Lag(A.id, 1) OVER(ORDER BY A.TIME) id2,
  9           Lag(A.name, 1) OVER(ORDER BY A.TIME) name2,
 10           Lag(A.sex, 1) OVER(ORDER BY A.TIME) sex2
 11       FROM T A
 12       ORDER BY A.TIME
 13  )
 14  WHERE id=id2 and name=name2 and sex=sex2
 15  ;

ID         NAME                 SEX        TIME
---------- -------------------- ---------- --------------
006        ff                   f          13-7月 -17
003        cc                   m          15-7月 -17

3.删除记录

方法一：

SQL> delete from t  where (t.id, t.name, t.sex, t.time)
  2  in (
  3  Select t1.id, t1.name,t1.sex, t1.time
  4  From
  5  (
  6      select id ,name,sex, time ,row_number() over(order by time) seq1  from t order by time
  7  ) t1
  8   inner join
  9  (
 10      select id ,name,sex, time ,row_number() over(order by time) seq2  from t order by time
 11  ) t2
 12  On t1.seq1=t2.seq2+1 and t1.id=t2.id and t1.name=t2.name and t1.sex=t2.sex
 13  )
 14  ;

2 rows deleted.

方法二：

SQL> delete from t  where (t.id, t.name, t.sex, t.time)
  2  in (
  3  SELECT id, name, sex, time
  4  FROM
  5  (
  6       SELECT A.ID,
  7           A.NAME,
  8           A.SEX,
  9           A.TIME,
 10           Lag(A.id, 1) OVER(ORDER BY A.TIME) id2,
 11           Lag(A.name, 1) OVER(ORDER BY A.TIME) name2,
 12           Lag(A.sex, 1) OVER(ORDER BY A.TIME) sex2
 13       FROM T A
 14       ORDER BY A.TIME
 15  )
 16  WHERE id=id2 and name=name2 and sex=sex2
 17  );

2 rows deleted.

4.查询结果:

SQL> select * from t order by time;

ID         NAME                 SE TIME
---------- -------------------- -- --------------
001        aa                   f  04-7月 -17
002        bb                   f  06-7月 -17
003        cc                   m  06-7月 -17
003        cc                   m  09-7月 -17
004        dd                   m  11-7月 -17
006        ff                   f  12-7月 -17
005        ee                   m  12-7月 -17
007        ff                   f  13-7月 -17
003        cc                   m  14-7月 -17
002        bb                   f  15-7月 -17

10 rows selected.

拓展思考：当要求查询相邻三条或多条是否相同时只需在lag函数以及查询条件上做相应修改即可。