语法
ROWNUM
该函数无需参数
返回numeric value,该值表示访问该表行的顺序
Example
CUSTOMER_ID LAST_NAME FIRST_NAME FAVORITE_WEBSITE
----------- --------- ---------- ---------------------
4000 Jackson Joe www.techonthenet.com
5000 Smith Jane www.digminecraft.com
6000 Ferguson Samantha www.bigactivities.com
7000 Reynolds Allen www.checkyourmath.com
8000 Anderson Paige
9000 Johnson Derek www.techonthenet.com
SELECT ROWNUM,CUSTOMERS.* FROM CUSTOMERS WHERE CUSTOMER_ID>4500;
结果如下:
ROWNUM CUSTOMER_ID LAST_NAME FIRST_NAME FAVORITE_WEBSITE
------ ----------- --------- ---------- ---------------------
1 5000 Smith Jane www.digminecraft.com
2 6000 Ferguson Samantha www.bigactivities.com
3 7000 Reynolds Allen www.checkyourmath.com
4 8000 Anderson Paige
5 9000 Johnson Derek www.techonthenet.com
ROWNUM 结合 Order By使用
SELECT ROWNUM, customers.*
FROM customers
WHERE customer_id > 4500
ORDER BY last_name;
结果如图示:
ROWNUM CUSTOMER_ID LAST_NAME FIRST_NAME FAVORITE_WEBSITE
------ ----------- --------- ---------- ---------------------
4 8000 Anderson Paige
2 6000 Ferguson Samantha www.bigactivities.com
5 9000 Johnson Derek www.techonthenet.com
3 7000 Reynolds Allen www.checkyourmath.com
1 5000 Smith Jane www.digminecraft.com
因为首先查新customer_id>4500的数据,此时依据访问表中的行的顺序以此给ROWNUM赋值,然后依据last_name进行ASC排序
推荐用法,将filter和sort语句放到子查询中作为新的结果集,把ROWNUM放在最外层的SELECT 语句中:
SELECT ROWNUM, a.*
FROM (SELECT customers.*
FROM customers
WHERE customer_id > 4500
ORDER BY last_name) a;
结果如图所示:
ROWNUM CUSTOMER_ID LAST_NAME FIRST_NAME FAVORITE_WEBSITE
------ ----------- --------- ---------- ---------------------
1 8000 Anderson Paige
2 6000 Ferguson Samantha www.bigactivities.com
3 9000 Johnson Derek www.techonthenet.com
4 7000 Reynolds Allen www.checkyourmath.com
5 5000 Smith Jane www.digminecraft.com
使用ROWNUM进行范围查找
SELECT *
FROM (SELECT customers.*
FROM customers
WHERE customer_id > 4500
ORDER BY last_name)
WHERE ROWNUM < 3;
该语句表示返回前两行的结果
CUSTOMER_ID LAST_NAME FIRST_NAME FAVORITE_WEBSITE
----------- --------- ---------- ---------------------
8000 Anderson Paige
6000 Ferguson Samantha www.bigactivities.com
SELECT *
FROM (SELECT customers.*
FROM customers
WHERE customer_id > 4500
ORDER BY last_name DESC)
WHERE ROWNUM < 3;
该语句表示返回后两行数据
CUSTOMER_ID LAST_NAME FIRST_NAME FAVORITE_WEBSITE
----------- --------- ---------- -------------------
5000 Smith Jane www.digminecraft.com
7000 Reynolds Allen www.checkyourmath.com
ROWNUM对结果进行过滤只能使用小于号
Student表 表结构为:
ID char(6) --学号
name varchar2(50) -- 姓名
create table student(ID char(6),name varchar2(50));
insert into sale values('200001',‘张一’);
insert into sale values('200002',‘王二’);
insert into sale values('200003',‘李三’);
insert into sale values('200004',‘赵四’);
commit;
因为rownum都是从1开始,但是1以上的自然数在rownum做等于判断是时认为都是false条件,所以无法查到rownum = n(n>1的自然数)。
select rownum,id,name from student where rownum=1;
查询大于2行以后的数据
select * from(select rownum no,id,name from student) where no >2;
查询小于3的数据
select * from student where rownum<3;
查询大于2小于3的数据
select * from (select rownum no, * from student where rownum<3 ) where no>2;
(4)rownum和排序
Oracle中的rownum的是在取数据的时候产生的序号,所以想对指定排序的数据去指定的rowmun行数据就必须注意了。
SQL> select rownum ,id,name from student order by name;
ROWNUM ID NAME
3 200003 李三
2 200002 王二
1 200001 张一
4 200004 赵四
可以看出,rownum并不是按照name列来生成的序号。系统是按照记录插入时的顺序给记录排的号,rowid也是顺序分配的。为了解决这个问题,必须使用子查询
SQL> select rownum ,id,name from (select * from student order by name);
ROWNUM ID NAME
1 200003 李三
2 200002 王二
3 200001 张一
4 200004 赵四
这样就成了按name排序,并且用rownum标出正确序号(有小到大)