程序员人生 网站导航

pl/sql利用rownum取得排序后的部分行序列

栏目:综合技术时间:2013-12-14 10:26:20

我们使用oracle的人都知道可以通过rownum伪列得到查询结果序列前面的指定的行,为了下面更好的进行说明问题,我们先来创建一个数据表table1:

create table table1

(AAA integer primary key,

BBB varchar(30));

然后在table1中插入9条数据:

insert into table1 values (8, 'good');

insert into table1 values (7, 'morning');

insert into table1 values (20, 'afternoon');

insert into table1 values (2, 'have');

insert into table1 values (19, 'boy');

insert into table1 values (30, 'girl');

insert into table1 values (15, 'left');

insert into table1 values (26, 'think');

insert into table1 values (98, 'beautiful');

commit;

现在使用:

Select * from table1 where rownum < 4;

来得到前三个行。

AAA     BBB

8 good

7 morning

20 afternoon

这没有问题,但如果你对rownum使用了大于号(>),则查询的结果集一定是空的。如:

Select * from table1 where rownum > 1;

无论表中有多少数据,都不会返回任何的数据。我们什么时候会用到rownum大于一个数字进行查询呢,这里先卖一个关子,后面再说。

再来说说排序和rownum的关系:

如果使用

Select * from table1 where rownum < 4 Order by AAA;

我这里查询出来的结果是:

AAA     BBB

7 morning

8 good

20 afternoon

和前面没有Order By时的结果集数据是一样的,只是排了一个序,而我们期望的结果是:

AAA     BBB

2 have

7 morning

8 good

那为什么会是这样的一个结果,而又如何才能得到我们期望的结果呢?我们先要搞清楚rownum是如何生成的。现在使用下面的语句查询一下:

select t.*, rownum from table1 t where rownum < 4 order by AAA ;

AAA     BBB     ROWNUM

7 morning 2

8 good 1

20 afternoon 3

发现rownum并不是按1,2,3的顺序排列的,为什么会是这样的一个结果呢?带着这个问题,我们再使用下面的这个语句进行查询:

select t.*, rownum, rowid from table1 t;

AAA BBB   Rownum        RowID

8 good     1 AAAY8QAABAAAVIaAAA

7 morning 2 AAAY8QAABAAAVIaAAB

20 afternoon 3 AAAY8QAABAAAVIaAAC

2 have 4 AAAY8QAABAAAVIaAAD

19 boy 5 AAAY8QAABAAAVIaAAE

30 girl 6 AAAY8QAABAAAVIaAAF

15 left 7 AAAY8QAABAAAVIaAAG

26 think     8 AAAY8QAABAAAVIaAAH

98 beautiful 9 AAAY8QAABAAAVIaAAI

看到这个结果我们猜想,oracle是插入数据的同时就为每一行建立了一个惟一的rowid,并且是按插入的顺序排序的,而rownum是按RowID进行排序的。为了证明我们的猜想是正确的,我们先删除AAA为19的行,再插入另一个AAA为34的行,语句分别如下:

delete from table1 where aaa = 19;

insert into table1 values (34, 'like');

现在我们再使用select t.*, rownum, rowid from table1 t;进行查询:

AAA BBB       Rownum       RowID

8 good     1 AAAY8QAABAAAVIaAAA

7 morning 2 AAAY8QAABAAAVIaAAB

20 afternoon 3 AAAY8QAABAAAVIaAAC

2 have 4 AAAY8QAABAAAVIaAAD

30 girl 5 AAAY8QAABAAAVIaAAF

15 left 6 AAAY8QAABAAAVIaAAG

26 think     7 AAAY8QAABAAAVIaAAH

98 beautiful 8 AAAY8QAABAAAVIaAAI

34   like       9   AAAY8QAABAAAVIaAAJ

结果证明我们的猜想是正确的:oracle是插入数据的同时就为每一行建立了一个惟一的rowid,并且是按插入的顺序排序的,而rownum是按RowID进行排序的。

现在问题就出来了,既然rownum是按照rowid进行排序的,而rowid我们是不能改变的,也就是说我们不能改变rownum的排序方式,那么如何才能取得排序后的前n行呢?

为了得到我们期望的排序后的前n行数据,我们使用子查询来实现,具体就是查询另一个查询语句返回的结果,sql语句如下:

select * from (select * from table1 t order by AAA) where rownum < 4 ;

返回的结果如下:

AAA BBB

2 have

7 morning

8 good

正是我们所期望的。

现在我们再来说说什么时候会用到rownum大于一个数字进行查询,而又如何实现。我们先来设想一下,在一个web页面上用一个table来显示数据,假设一页显示4行数据,现在我们要显示第二页的数据,我们怎么实现呢?dotnet的datagrid和gridview使用的非常笨拙低效的办法:把所有数据查出来,然后只显示指定的数据。为了更有效的处理这个问题,我们想只查询出来第二页的数据,这就要用到rownum的大于查询。我们期望使用

select * from (select * from table1 t order by AAA) where rownum > 4 and rownum <= 8;

来得到结果,可是前面说过,任何使用rownum大于一个数据的查询,都不会返回任何的结果集。那我们又如何才能得到我们期望的结果呢?这就要使用一点技巧了,先使用:

select * from (select * from table1 t order by AAA) where rownum <= 8;

返回排序后的前8行数据,但只是这样我们仍无法再过滤掉前面的四行数据。为此我们要使用这个查询的外层查询的临时rownum(可能有点拗口,再解释一下,我们要使用这个rownum不是表table1的rownum,而查询(select * from table1 t order by AAA)查询结果集在查询时生成的rownum),并对它重命名,然后再对这个返回结果进行查询,得到我们期望的结果,查询语句如下:

Select * from (select a.*, rownum rn from (select * from table1 t order by AAA) where rownum <= 8) where rn > 4;

AAA BBB RN

20 afternoon 5

26 think 6

30 girl 7

34 like 8

这样,我们就成功的实现了利用rownum查询出排序后的中间部分行的功能。

在此我把这个功能完整的写出来,希望对有用到这个功能的朋友有所帮助,谢谢。

原文:http://www.cnblogs.com/mickeychang/archive/2009/08/23/1552578.html

------分隔线----------------------------
------分隔线----------------------------

最新技术推荐