关于SELECTN问题

这里描述的 SELECT N 包括这样几种情况:

1. 选取TOP N行记录

2. 选取N1-N2行记录

3. 选取FOOT N行记录

当然需要考虑是否有ORDER BY子句的情况, 下面试以系统视图CAT为例分别说明.

注: A. 为没有ORDER BY的情况

B. 有ORDER BY的情况

1. 选取 TOP N 行记录

A. SELECT * FROM CAT WHERE ROWNUM

B. SELECT * FROM

( SELECT * FROM CAT ORDER BY TABLE_TYPE )

WHERE ROWNUM

2. 选取N1-N2行记录

A. SELECT TABLE_NAME,TABLE_TYPE FROM

( SELECT ROWNUM ROWSEQ,TABLE_NAME,TABLE_TYPE FROM CAT )

WHERE ROWSEQ BETWEEN N1 AND N2;

或:

SELECT * FROM CAT WHERE ROWNUM

MINUS

SELECT * FROM CAT WHERE ROWNUM

B. SELECT TABLE_NAME,TABLE_TYPE FROM

( SELECT ROWNUM ROWSEQ,X.* FROM (SELECT * FROM CAT ORDER BY TABLE_TYPE) X)

WHERE ROWSEQ BETWEEN N1+1 AND N2;

3. 选取FOOT N行记录

这里是说明不知道记录集的记录个数的情况, 如果已知, 用上面2的方法即可

A. SELECT TABLE_NAME,TABLE_TYPE FROM

( SELECT ROWNUM ROWSEQ,TABLE_NAME,TABLE_TYPE FROM CAT )

WHERE ROWSEQ ( SELECT COUNT(*)-N FROM CAT )

B. SELECT TABLE_NAME,TABLE_TYPE FROM

( SELECT ROWNUM ROWSEQ,TABLE_NAME,TABLE_TYPE FROM CAT ORDER BY TABLE_TYPE)

WHERE ROWSEQ ( SELECT COUNT(*)-N FROM CAT )

或

SELECT * FROM

( SELECT TABLE_NAME,TABLE_TYPE FROM CAT ORDER BY TABLE_TYPE DESC)

WHERE ROWNUM

以上在ORACLE8.1.5 for Windows2000pro 上测试通过