一生懸命

Oracle SQL 技巧1- -

Tag： Oracle    SQL    技巧1                                          

 

Oracle SQL 技巧1：

在使用rownum伪列时，如果在查询时让它大于某个值，那是不会返回任何结果的，那么当我们要在一个rownum的区间查询时该如何做呢，看如下的例子吧。

 

E:\>sqlplus kxy01/kxy01@xyhome

 

SQL*Plus: Release 9.0.1.0.1 - Production on 星期六 8月 27 16:09:17 2005

 

(c) Copyright 2001 Oracle Corporation.  All rights reserved.

 

 

连接到:

Oracle9i Enterprise Edition Release 9.0.1.1.1 - Production

With the Partitioning option

JServer Release 9.0.1.1.1 - Production

//首先建立一个测试用的表test05827

SQL> select * from tab;

 

未选定行

 

SQL>  create table test05827 ( id  number(3) , name varchar(15) ,val number(5));

 

表已创建。

 

SQL> select * from tab;

 

TNAME                          TABTYPE  CLUSTERID

------------------------------ ------- ----------

TEST05827                      TABLE

//建立完成，然后向里面插入一些数据，数据插入后结果如下

SQL> select * from test05827;

 

        ID NAME                   VAL

---------- --------------- ----------

         1 kxy                    100

         2 kxy                    105

       101 kxy                    150

       102 kxy                    155

        51 df                     160

        52 df                     165

       151 df                     210

       152 df                     215

       101 tw                     230

       102 tw                     235

       201 tw                     280

 

        ID NAME                   VAL

---------- --------------- ----------

       202 tw                     285

 

已选择12行。

//通过下面的方法将数据再增加些

SQL> create table test05827bak as select * from test05827;

 

表已创建。

 

SQL> update test05827bak set id=id+200,val=val+23;

 

已更新12行。

 

SQL> insert into test05827 value select * from test05827bak;

 

已创建12行。

 

SQL> select count(*) from test05827;

 

  COUNT(*)

----------

        24

//数据构造完毕

SQL> select * from test05827 where name='kxy';

 

        ID NAME                   VAL

---------- --------------- ----------

         1 kxy                    100

         2 kxy                    105

       101 kxy                    150

       102 kxy                    155

       201 kxy                    123

       202 kxy                    128

       301 kxy                    173

       302 kxy                    178

 

已选择8行。

//现试一试rownum小于某值的情况

SQL> select * from test05827 where name='kxy' and rownum<4;

 

        ID NAME                   VAL

---------- --------------- ----------

         1 kxy                    100

         2 kxy                    105

101 kxy                    150

//再试一试rownum大于某值的情况

SQL>  select * from test05827 where name='kxy' and rownum>4;

 

未选定行

 

SQL>  select * from test05827 where name='kxy' and rownum>3 and rownum<5;

 

未选定行

//我们可以使用如下的嵌套查询来建立一个包含rownum列的子查询

SQL> select * from ( select id,name,val,row_number()over(order by name)as num fr

om test05827);

 

        ID NAME                   VAL        NUM

---------- --------------- ---------- ----------

        51 df                     160          1

        52 df                     165          2

       152 df                     215          3

       251 df                     183          4

       151 df                     210          5

       252 df                     188          6

       352 df                     238          7

       351 df                     233          8

         1 kxy                    100          9

         2 kxy                    105         10

       101 kxy                    150         11

 

        ID NAME                   VAL        NUM

---------- --------------- ---------- ----------

       202 kxy                    128         12

       302 kxy                    178         13

       301 kxy                    173         14

       201 kxy                    123         15

       102 kxy                    155         16

       101 tw                     230         17

       302 tw                     258         18

       402 tw                     308         19

       401 tw                     303         20

       301 tw                     253         21

       102 tw                     235         22

 

        ID NAME                   VAL        NUM

---------- --------------- ---------- ----------

       201 tw                     280         23

       202 tw                     285         24

 

已选择24行。

 

SQL> select * from (select max(id),name,max(val),row_number()over(order by name)

as num from test05827 group by name);

 

   MAX(ID) NAME              MAX(VAL)        NUM

---------- --------------- ---------- ----------

       352 df                     238          1

       302 kxy                    178          2

402 tw                     308          3

//然后在嵌套子查询的基础上，就可以进行rownum的区间查询了。

SQL> select * from (select id,name,val,row_number()over(order by name) as num fr

om test05827) where num>10 and num<24;

 

        ID NAME                   VAL        NUM

---------- --------------- ---------- ----------

       101 kxy                    150         11

       202 kxy                    128         12

       302 kxy                    178         13

       301 kxy                    173         14

       201 kxy                    123         15

       102 kxy                    155         16

       101 tw                     230         17

       302 tw                     258         18

       402 tw                     308         19

       401 tw                     303         20

       301 tw                     253         21

 

        ID NAME                   VAL        NUM

---------- --------------- ---------- ----------

       102 tw                     235         22

       201 tw                     280         23

 

已选择13行。

 

以上。

- 作者： kangxy 访问统计：　2005年08月27日, 星期六 17:22 加入博采