oracle rownum的实践-凯发app官方网站

凯发app官方网站-凯发k8官网下载客户端中心 | | 凯发app官方网站-凯发k8官网下载客户端中心
  • 博客访问: 3977559
  • 博文数量: 536
  • 博客积分: 10470
  • 博客等级: 上将
  • 技术积分: 4825
  • 用 户 组: 普通用户
  • 注册时间: 2006-05-26 14:08
文章分类

全部博文(536)

文章存档

2024年(3)

2021年(1)

2019年(1)

2017年(1)

2016年(2)

2013年(2)

2012年(10)

2011年(43)

2010年(10)

2009年(17)

2008年(121)

2007年(252)

2006年(73)

相关博文
  • ·
  • ·
  • ·
  • ·
  • ·
  • ·
  • ·
  • ·
  • ·
  • ·

分类: oracle

2006-12-14 17:58:06

[测试oracle版本为9i, 系统rs3]
实验总结:
   rownum同rowid有很多相同点, 都是伪列,它与数据表的行是对应的,它的值是不会随任何操作改变的。

有一个表,结构如下:
sql> desc aa;
 名称                                      是否为空? 类型
 ----------------------------------------- -------- ----------------------------
 a                                                  number(38)
 b                                                  char(10)

表中的数据及rownum值。
sql> select rownum, aa.* from aa;

    rownum          a b                                                         
---------- ---------- --------------------                                      
         1        123 1afssf                                                    
         2          4 afa                                                       
         3         52 afrt3                                                     
         4          9 fasf3                                                     
         5         89 asf323                                                    
         6         12 4124                                                      
         7          1 23523                                                     
         8         23 2352afff                                                  
         9          9 afsdgasg                                                  
        10         13 325235                                                    

已选择10行。

我们看一下排序后的rownum值,与上面进行比较就可以得出,每行对应的rownum值没有变。
sql> select rownum, aa.* from aa order by aa.a;

    rownum          a b                                                         
---------- ---------- --------------------                                      
         7          1 23523                                                     
         2          4 afa                                                       
         4          9 fasf3                                                     
         9          9 afsdgasg                                                  
         6         12 4124                                                      
        10         13 325235                                                    
         8         23 2352afff                                                  
         3         52 afrt3                                                     
         5         89 asf323                                                    
         1        123 1afssf                                                    

已选择10行。

这个是要多加注意的地方了。该语句的功能是先将aa表排序,再从排序的结果中先取rownum为3到7的数据。注意排序的过程原来的rownum是不会改变的。也就是说该语句的功能不是先将aa表排序,再从排序的结果中取得3到7行的数据,可能人为的会认为是:

    rownum          a b                                                         
---------- ---------- --------------------  
         3          9 fasf3                                                     
         4          9 afsdgasg                                                  
         5         12 4124                                                      
         6         13 325235                                                    
         7         23 2352afff      
这才是真实的结果:
sql> select r, a, b from (select rownum r, a, b from aa order by a) where r between 3 and 8;

         r          a b                                                         
---------- ---------- --------------------                                      
         7          1 23523                                                     
         4          9 fasf3                                                     
         6         12 4124                                                      
         8         23 2352afff                                                  
         3         52 afrt3                                                     
         5         89 asf323                                                    

已选择6行。

minus是找到两个给定的数据集合之间的差异,即找到一个数据集合,该集合的数据是仅存在于前一个数据集而后一个数据集中不存在的,相当于集合1-集合2。
sql> select r, a, b from (select rownum r, a, b from aa order by a ) where r < 8
  2  minus
  3  select r, a, b from (select rownum r, a, b from aa order by a ) where r < 3;

         r          a b                                                        
---------- ---------- --------------------                                     
         3         52 afrt3                                                    
         4          9 fasf3                                                    
         5         89 asf323                                                   
         6         12 4124                                                     
         7          1 23523                                                    

已选择6行。

sql>  select r, a, b from (select rownum r, a, b from aa order by a ) where r < 8;

         r          a b
---------- ---------- --------------------
         7          1 23523
         2          4 afa
         4          9 fasf3
         6         12 4124
         3         52 afrt3
         5         89 asf323
         1        123 1afssf

已选择7行。

sql> select r, a, b from (select rownum r, a, b from aa order by a ) where r < 3;

         r          a b
---------- ---------- --------------------
         2          4 afa
         1        123 1afssf
阅读(1859) | 评论(0) | 转发(0) |
0

上一篇:pthread_join()

下一篇:delete指定的行(rownum)

给主人留下些什么吧!~~
")); function link(t){ var href= $(t).attr('href'); href ="?url=" encodeuricomponent(location.href); $(t).attr('href',href); //setcookie("returnouturl", location.href, 60, "/"); }
网站地图