以学生表为例
insert into Student01 values('01' , '赵雷' , to_date('1990-01-01','yyyy-mm-dd'), '男');
insert into Student01 values('02' , '钱电' , to_date('1990-12-21','yyyy-mm-dd'), '男');
insert into Student01 values('03' , '孙风' , to_date('1990-12-20','yyyy-mm-dd'), '男');
insert into Student01 values('04' , '李云' , to_date('1990-12-06','yyyy-mm-dd'), '男');
insert into Student01 values('05' , '周梅' , to_date('1991-12-01','yyyy-mm-dd'), '女');
insert into Student01 values('06' , '吴兰' , to_date('1992-01-01','yyyy-mm-dd'), '女');
insert into Student01 values('07' , '郑竹' , to_date('1989-01-01','yyyy-mm-dd'), '女');
insert into Student01 values('09' , '张三' , to_date('2017-12-20','yyyy-mm-dd'), '女');
insert into Student01 values('10' , '李四' , to_date('2017-12-25','yyyy-mm-dd'), '女');
insert into Student01 values('11' , '李四' , to_date('2012-06-06','yyyy-mm-dd'), '女');
insert into Student01 values('12' , '赵六' , to_date('2013-06-13','yyyy-mm-dd'), '女');
insert into Student01 values('13' , '孙七' , to_date('2014-06-01','yyyy-mm-dd'), '女');
使用开窗函数对时间字段进行排序
select row_number()over( order by Sage desc) rn,t.* from student01 t
union all
select row_number()over( order by Sage asc) ,t1.* from student01 t1
最后再加上delete就可以啦
delete from student01 where SId not in(
select SID FROM(
select row_number()over( order by Sage desc) rn,t.* from student01 t
union all
select row_number()over( order by Sage asc) ,t1.* from student01 t1
)
WHERE RN =1
)