6 rows selected SQL> delete from t1 where time not in 2 ( 3 select time 4 from 5 ( 6 select time,row_number() over(order by time desc) rn 7 from 8 t1 9 ) 10 where rn <= 3 11 ) 12 ;
3 rows deleted
SQL> select * from t1 order by time desc;
TIME ------------------------------ 2010-6-4 16:55:14 2010-6-3 16:55:17 2010-6-2 16:55:19
2.用order by 查出table_a前1000条数据 insert 到另外一张表table_b里
3.truncate table table_a
4.把table_b里的数据insert到table_a如果数据量小的话,会更简单
--举个删除超过3行的例子 楼主可以参考一下
SQL> select * from t1 order by time desc;
TIME
------------------------------
2010-6-4 16:55:14
2010-6-3 16:55:17
2010-6-2 16:55:19
2010-6-1 16:55:21
2010-5-31 16:55:23
2010-5-30 16:55:25
6 rows selected
SQL> delete from t1 where time not in
2 (
3 select time
4 from
5 (
6 select time,row_number() over(order by time desc) rn
7 from
8 t1
9 )
10 where rn <= 3
11 )
12 ;
3 rows deleted
SQL> select * from t1 order by time desc;
TIME
------------------------------
2010-6-4 16:55:14
2010-6-3 16:55:17
2010-6-2 16:55:19