table name : tradecard_no number(10) pk
riding_time date pk
money number(6,2) not null
group number(10) not null
worker_no number(10) not null
riding_time date pk
money number(6,2) not null
group number(10) not null
worker_no number(10) not null
(select card_no,riding_time from (select rownum rw,card_no,riding_time from trade where card_no in (select distinct card_no from trade) order by card_no,riding_time desc) where rw<=10);
做一个游标,
cursor c1 is select distinct card_no from trade;
做个循坏,逐条删除
delete from trade where (card_no,riding_time) not in
(select card_no,riding_time from (select rownum rw,card_no,riding_time from trade where card_no=c1.card_no order by card_no,riding_time desc) where rw<=10);
SQL> select * from trade; CARD_NO RIDING_TIME
----------- -----------
1 2003-5-7 11:35:09
1 2003-4-7 11:36:15
1 2003-3-7 11:36:52
1 2003-2-7 11:36:57
1 2002-12-7 11:37:04
1 2003-1-7 11:37:09
2 2003-4-7 11:37:25
2 2003-3-7 11:37:36
2 2002-11-7 11:37:41
2 2002-9-7 11:37:45
2 2002-7-7 11:38:0711 rows selectedSQL> select card_no,riding_time from (select card_no,riding_time, rank() over (PARTITION BY card_no order by riding_time desc) as "rank" from trade)
2 where "rank"<=3; CARD_NO RIDING_TIME
----------- -----------
1 2003-5-7 11:35:09
1 2003-4-7 11:36:15
1 2003-3-7 11:36:52
2 2003-4-7 11:37:25
2 2003-3-7 11:37:36
2 2002-11-7 11:37:416 rows selected
或建一个存贮过程,在程序中引用。
create procedure del_much as
begin
delete from (select * from your_tb order by riding_time desc) tab where tab.rownum>10;
end;
delete from (select * from your_tb order by riding_time desc) tab where tab.rownum>10;
commit;
或建一个存贮过程,在程序中引用。
create procedure del_much as
begin
delete from (select * from your_tb order by riding_time desc) tab where tab.rownum>10;
commit;
end;
http://download-west.oracle.com/docs/cd/A87860_01/doc/server.817/a85397/function.htm#85757
利用create table new_tabel as select ....;
清空原表,
insert into old_table select ....from new_table;
drop table new_table;