比较常用的写法我想应该是:
select name, address, sex ...
from (select rownum rno, name, address, sex ...
from mytable
where rownum <= 50
order by name desc)
where rno >= 30;
select username,rownum from dba_users
where rownum < 4 order by username;
USERNAME ROWNUM
-------------------------- ------
OUTLN 3
SYS 1
SYSTEM 2
这样就可以通过取得记录然后排序解决问题:
SELECT username,rownum FROM
(SELECT username FROM dba_users ORDER BY username)
WHERE ROWNUM < 4;
USERNAME ROWNUM
-------------------------- ------
AURORA$ORB$UNAUTHENTICATED 1
CTXSYS 2
DBSNMP 3
一个(>)符号和 rownum 一起使用时遇到负数则不会有结果:
1 SELECT username,rownum FROM
2 (SELECT username FROM dba_users ORDER BY username)
3* WHERE ROWNUM > 4
SQL> /
no rows selected
要显示最末的三条记录就不能使用(>):
1 SELECT username,rownum FROM
2 (SELECT username FROM dba_users ORDER BY username desc)
3* WHERE ROWNUM < 4
SQL> /
USERNAME ROWNUM
-------------------------- ------
TESTUSER 1
SYSTEM 2
SYS 3
杩欎竴鍙ユ槸姹侷D鍙峰湪15鍒?00涔嬮棿鐨勮褰曪紝浣犳敞鎰忚浆鍖栦竴涓嬪惂锛?
select caseregister from (select rank() over (order by caseregister desc) rank1 caseregister from caseregister order by
caseregister desc ) ss where ss.rank1>15 and ss.rank1<100 order by caseregister ;