rownum 是个伪列,在你的任何SQL里面加上它,它会安顺序加1. 如: create table temp(name varchar2(20)); insert into temp values('A'); insert into temp values('B'); insert into temp values('C');Select rownum, name from temp; 返回: ROWNUM NAME ------- -------------------- 1 A 2 B 3 C select name from temp where rownum<2; 返回: NAME ------- A
需要注意的是: create table temp(name varchar2(20)); insert into temp values('A'); insert into temp values('B'); insert into temp values('C');Select rownum, name from temp; 返回: ROWNUM NAME ------- -------------------- 1 B 2 A 3 C select name from temp where rownum<2; 返回: NAME ------- B 而select name from (select name from temp order by name) where rownum <2 ; 返回: NAME ------- A rownum指的是当前的序列号,所以它的指令优先级高于order by,所以如果你想按照某些顺序取信息的话,必须先排序后用rownum。
小声的问一下 为什么select name from (select name from temp order by name) where rownum >=2 ;却提示未选定行呢? 谢谢
select name from (select name from temp order by name) where rownum >=2没有问题
可是为何我这里 SQL*Plus: Release 9.2.0.1.0 - Production on 星期三 11月 10 17:53:44 2004Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved. 连接到: Personal Oracle9i Release 9.2.0.1.0 - Production With the Partitioning, OLAP and Oracle Data Mining options JServer Release 9.2.0.1.0 - ProductionSQL> select *from tmp;NAME ---------- 张 黄 A B C D已选择6行。SQL> select name from (select name from tmp order by name) where rownum >=2;未选定行SQL> 对不起,我是菜鸟,请指点一下:)
如:
create table temp(name varchar2(20));
insert into temp values('A');
insert into temp values('B');
insert into temp values('C');Select rownum, name from temp;
返回:
ROWNUM NAME
------- --------------------
1 A
2 B
3 C
select name from temp where rownum<2;
返回:
NAME
-------
A
create table temp(name varchar2(20));
insert into temp values('A');
insert into temp values('B');
insert into temp values('C');Select rownum, name from temp;
返回:
ROWNUM NAME
------- --------------------
1 B
2 A
3 C
select name from temp where rownum<2;
返回:
NAME
-------
B
而select name from (select name from temp order by name) where rownum <2 ;
返回:
NAME
-------
A
rownum指的是当前的序列号,所以它的指令优先级高于order by,所以如果你想按照某些顺序取信息的话,必须先排序后用rownum。
num为字段的别名,可是它不是rownum的别名吗?把最后个num改成rownum结果是未选定行,这里可以在解释下吗
关于rownum,我理解了些了,谢谢楼上各位。
为什么select name from (select name from temp order by name) where rownum >=2 ;却提示未选定行呢?
谢谢
SQL*Plus: Release 9.2.0.1.0 - Production on 星期三 11月 10 17:53:44 2004Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
连接到:
Personal Oracle9i Release 9.2.0.1.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.1.0 - ProductionSQL> select *from tmp;NAME
----------
张
黄
A
B
C
D已选择6行。SQL> select name from (select name from tmp order by name) where rownum >=2;未选定行SQL>
对不起,我是菜鸟,请指点一下:)