删除重复的记录,并且要按时间先后删除,把最近的那条记录保留如:date instance status number2007-4-2 15:30:13 0 A 2001001
2007-4-2 15:28:13 0 B 2001001
2007-4-2 15:15:12 0 A 2001001
2007-4-2 15:30:13 0 A 2001007需要得到的结果是
date instance status number2007-4-2 15:30:13 0 A 2001001
2007-4-2 15:30:13 0 A 2001007
高手指点
2007-4-2 15:28:13 0 B 2001001
2007-4-2 15:15:12 0 A 2001001
2007-4-2 15:30:13 0 A 2001007需要得到的结果是
date instance status number2007-4-2 15:30:13 0 A 2001001
2007-4-2 15:30:13 0 A 2001007
高手指点
第一步,按以下方式查询,调试使其正常运行(数据库不同,sql也有微小的差异)
select date ,instance,status,number from Test as a
where date<>
(select max(date) from Test
where a.number=number)
第二步,执行删除
del TEST where number in
(select number from(select date ,instance,status,number from Test as a
where date<>
(select max(date) from Test
where a.number=number))
where to_char(date,'yyyy-mm-dd:HH:ss')<>
(select max(to_char(date,'yyyy-mm-dd:HH:ss')) from Test
where a.number=number)
(select number from(select date ,instance,status,number from Test as a
where date<>
(select max(date) from Test
where a.number=number))
and rownum<=100
删完后把那个rownum改成200,然后依此递增,删! 删上3万次就OK了!!
上面感觉总要扫两次表。