问题描述:
将表数据根据时间进行排序,若其中有相邻两条记录,除了时间字段外均相同,则删除两条中较新的一条记录(如下图)
解决方法:
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函数以及查询条件上做相应修改即可。