ITPub博客

首页 > 应用开发 > IT综合 > Rowid和Rownum的区别?

Rowid和Rownum的区别?

原创 IT综合 作者:moonsoft 时间:2019-07-10 18:36:08 0 删除 编辑
rowid和rownum都是虚列,但含义完全不同。rowid是物理地址,用于定位oracle中具体数据的物理存储位置,而rownum则是sql的输出结果排序,从下面的例子可以看出其中的区别。[@more@]



SQL> select rowid,deptno from dept order by deptno;

ROWID DEPTNO
------------------ ----------
AAABe/AAFAAABLqAAA 10
AAABe/AAFAAABLqAAB 20
AAABe/AAFAAABLqAAC 30
AAABe/AAFAAABLqAAD 40

SQL> edit
Wrote file afiedt.buf

1* select rowid,deptno from dept order by deptno desc
SQL> /

ROWID DEPTNO
------------------ ----------
AAABe/AAFAAABLqAAD 40
AAABe/AAFAAABLqAAC 30
AAABe/AAFAAABLqAAB 20
AAABe/AAFAAABLqAAA 10

SQL> edit
Wrote file afiedt.buf

1* select rownum,deptno from dept order by deptno
SQL> /

ROWNUM DEPTNO
---------- ----------
1 10
2 20
3 30
4 40

SQL> edit
Wrote file afiedt.buf

1* select rownum,deptno from dept order by deptno desc
SQL> /

ROWNUM DEPTNO
---------- ----------
1 40
2 30
3 20
4 10

just think about ROWNUM pseudo column as a 'magic'. You can do

where rownum < n

or

where rownum = 1

but NOT

where rownum > n

or

where rownum = some number > 1

If you have to you can do something like:

select * from(
select t.*,rownum rn from t
)
where rn = 3

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/226700/viewspace-871292/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2005-01-20

  • 博文量
    412
  • 访问量
    331922