ORACLE中取出表中按照某字段排序的前N条记录,再把这前N条记录删除？

[wenli83]

取出表中按照某字段排序的前N条记录,然后要把这前N条记录删除
如何实现?

[mantisXF]

ORDER BY FIELD  ->  WHERE ROWNUM <= N  ->  DELETE

[Quote=引用楼主 wenli83 的帖子:]
取出表中按照某字段排序的前N条记录,然后要把这前N条记录删除
如何实现?
[/Quote]

[oracledbalgtu]

DELETE FROM T
WHERE ROWID IN
(SELECT ROWID
FROM (SELECT ROWID, ROW_NUMBER() OVER(ORDER BY COLX) RN FROM T)
WHERE RN <= 10);


[Quote=引用楼主 wenli83 的帖子:]
取出表中按照某字段排序的前N条记录,然后要把这前N条记录删除
如何实现?
[/Quote]

[wenli83]

[Quote=引用 2 楼 oracledbalgtu 的回复:]
DELETE FROM T
WHERE ROWID IN
(SELECT ROWID
FROM (SELECT ROWID, ROW_NUMBER() OVER(ORDER BY COLX) RN FROM T)
WHERE RN <= 10);


引用楼主 wenli83 的帖子:
取出表中按照某字段排序的前N条记录,然后要把这前N条记录删除
如何实现?
[/Quote]
我试试

[hyrongg]

SQL> select * from t;

                                    IDX CNAME
--------------------------------------- ------------------------------
                                      1 a
                                      2 b
                                      3 c
                                      4 d
                                      5 e

SQL> 
SQL> delete from t where idx in(
  2    select * from (
  3      select idx from t order by idx desc
  4    ) a where rownum<=2
  5  );

2 rows deleted

SQL> select * from t;

                                    IDX CNAME
--------------------------------------- ------------------------------
                                      1 a
                                      2 b
                                      3 c

[sleepzzzzz]

好代码啊.

[Quote=引用 2 楼 oracledbalgtu 的回复:]
DELETE FROM T
WHERE ROWID IN
(SELECT ROWID
FROM (SELECT ROWID, ROW_NUMBER() OVER(ORDER BY COLX) RN FROM T)
WHERE RN <= 10);


引用楼主 wenli83 的帖子:
取出表中按照某字段排序的前N条记录,然后要把这前N条记录删除
如何实现?
[/Quote]