第二行是2,我们可以采用limit语句进行分页

作者: www.9159.com  发布:2019-11-05

解释:红色为以什么分组   蓝色为以什么排序  紫色为表名   目前是获取表中每个 IDCARD中时间最大的一条

rownum

在Oralce中有一个伪列rownum,其在创建表的时候就存在了却不显示,若要使用这个列可以直接调用即可,也可以对这个列添加别名来调用。

rownum只能用于显示小于某行的数据即第一行开始到你要查询到的那一行为止的数据。

rownum对于小于某值的查询条件

select * from emp where rownum<=3; 查询emp前三行记录

rownum对于等于某个值得查询条件

select * from emp where rownum=1; 显示第一行记录

图片 1

1 引言

**查询rownum在某区间的数据,必须使用子查询例如要查询rownum在第二行到第三行之间的数据,包括第二行和第三行数据,那么我们只能写以下语句,先让它返回小于等于三的记录行,然后在主查询中判断新的rownum的别名列大于等于二的记录行。但是这样的操作会在大数据集中影响速度。
SQL> select * from (select rownum no**,id,name from student where rownum<=3 ) where no >=2;
        NO ID     NAME

 

分页

在Oracle把查询第几行到第几行的操作称为分页,其具体操作是通过子查询等操作完成。

select 列名 from (select 表名.*,rownum rn from 表名)表名  where rn操作;

思考如下:

1.选择所有内容

select * from emp;

2.显示rownum

select e.*,rownum rn from(select * from emp)e;

这一步可以精简为下面形式,但某些情况只能用上面那种

select emp.*,rownum rn from emp;

3.查询

select * from(select e.*,rownum rn from (select * from emp)e);

第二行是2,我们可以采用limit语句进行分页。4.其他变化

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

查询第三行的信息

select * from(select e.*,rownum rn from (select * from emp)e where rownum<=10) where rn>2;

查询第三到第十行信息。上面的也可以精简为

select * from(select emp.*,rownum rn from emp) where rn>2 and rn<=10;

图片 2

在某些时候我们需要先对表的内容进行排序,随后查询第x行到第y行的内容,这个时候有一个需要注意的点是rownum是在表产生的时候产生的伪列,所以使用排序会连着rownum的值进行排序,从而达不到想要的效果。

select rownum,sal from emp order by sal;

图片 3

为了解决上述这个问题,我们需要使用子表查询即先排好序,再在新表之中显示rownum来规避这个问题。

select rownum,sal.* from (select sal from emp order by sal)sal;

图片 4

考虑到排序的问题,所以在上方第二步的时候使用第一种方法即select e.*,rownum rn from(select * from emp)e;,在内表select * from emp中进行排序可以完成在乱序中找到第x行到第y行的效果。

select * from(select e.*,rownum rn from(select * from emp order by sal)e)where rn>=3 and rn<=8;

输出emp表薪资排名3到8的员工信息。

图片 5

图片 6

要先排序再选则须用select嵌套:内层排序外层选。
rownum是随着结果集生成的,一旦生成,就不会变化了;同时,生成的结果是依次递加的,没有1就永远不会有2!
rownum 是在查询集合产生的过程中产生的伪列,并且如果where件中存在 rownum 件的话,则:

select * from(select row_number()
over(partition by IDCARD order by DATATM desc) as rownum,*
from (SELECT
*
FROM
TABLENAME
)as H1
) as H where H.rownum =
1

在Oracle中有一个方法rownum用来查询第一行到第n行的内容,但没有一个合适的方法若查询第x行到第y行的内容,而在实际应用中却经常需要查询第x行到第y行的内容,这时我们就需要使用rownum和子表查询等内容来进行查询,因为这一块内容属于Oracle总的常用部分所以专门在此介绍。

如果我们是通过JDBC的方式访问数据库,那么就有必要根据数据库类型采取不同的SQL分页语句,对于MySql数据库,我们可以采用limit语句进行分页,对于Oracle数据库,我们可以采用rownum的方式进行分页.

         3 200003 李三
         4 200004 赵四

Oracle中的rownum一般是在实现分页查询时用到,虽然在我做的项目中只实现了分页显示而没有实现真正的分页,但是出于学习的目的研究了一下oracle的rownum。在使用查询语句时,我们经常要求返回表中的前n条记录或者是中间的几条记录,比如在一个大表(假设有10W条数据)要求查询从第1000到1005条的记录。面对这种查询,我们怎么办呢?mysql和oracle都有自己的解决办法。


2 MySql中的实现c

MySql的Limit m,n语句

Limit后的两个参数中,参数m是起始下标,它从0开始;参数n是返回的记录数。我们需要分页的话指定这两个值即可

在mysql中,我们可以使用limit语句来实现:

1)查询从第1000到1005条的记录(注意mysql中的记录是从0开始编号的,所以第1000条记录编号为999)

select * from table_name limit 999,5;

2)查询前10条记录

select * from table_name limit 10;

等价于:

select * from table_name limit 0,10;

2)查询从第100条记录开始到表的最后一条记录

select * from table_name limit 99 -l;

mysql提供-L的参数,表示到表的最后一条记录

对于rownum来说它是oracle系统顺序分配为从查询返回的行的编号,返回的第一行分配的是1,第二行是2,依此类推,这个伪字段可以用于限制查询返回的总行数,且rownum不能以任何表的名称作为前缀。

4 结束语

Oracle中的rownum与mysql的limit实现的功能相同,但没有mysql来的容易,它一般通过一个子查询来实现。mysql的易用性也是它能够纵横开源数据库的原因,它不像postgresql那样的学院派,它的那种简单易用性或许在大型软件项目的开发中值得借鉴。最近听说sql server 2008也实现了limit的查询,不过还没去试过,Oracle在这方面也要加油啊,用户容易使用才是王道。

感谢百度空间 yinaddress的分享

3 Oracle中的实现

Oracle使用rownum的关键字来实现这种查询:

首先我们假设有一个地域信息表area,其表结构如下图所示:

图片 7

表中的数据如下图所示(select * from area语句得到的结果):

 

图片 8

1)查询表中的前8条记录
select * from area where rownum <= 8
查询结果如下:

 

图片 9

2)查询第2到第8条记录
对于这种形式的查询,oracle不像mysql那么方便,它必须使用子查询或者是集合操作来实现。我们可以使用以下3种方式可以实现:

A: select id,province,city,district from (select id,province,city,district,rownum as num from area) where num between 2 and 8;

首先根据select id,province,city,district,rownum as num from area得到一个临时表,这个临时表中有一个rownum列(一个伪列,类似与rowid,但又不同于rowid,因为rowid是物理存在的一个列,也就是说Oracle数据库中任何一个表都有一个rowid列,而rownum不是物理存在的),然后在临时表中来查询。

B: select * from area where rownum <= 8 minus select * from area where rownum < 2;

使用集合减运算符minus,该操作返回在第一个select中出现而不在第二个select中出现的记录。

C: select id,province,city,district from (select id,province,city,district,rownum as num from area) where num >=2

intersect

select * from area where rownum <= 8;

使用集合交运算符intersect,这里绕了一个弯(不过这个弯实现了rownum大于某个数的查询),它是首先利用A的方式查询得到所有rownum大于2的记录,然后再与rownum小于等于8的记录集合做交运算。三种操作得到的结果一样,如下图所示:

 

图片 10

3)rownum需要注意的问题

[1] rownum不支持以下方式的查询

a: select * from area where rownum > 2;

b: select * from area where rownum = n;  --where n is a integer number lager than 1

注:rownum只支持select * from area where rownum =1的查询。Oracle的官方文档说明如下:

Conditions testing for ROWNUM values greater than a positive integer are always false.

For example, this query returns no rows:

SELECT * FROM employees

WHERE ROWNUM > 1;

The first row fetched is assigned a ROWNUM of 1 and makes the condition false. The

second row to be fetched is now the first row and is also assigned a ROWNUM of 1 and

makes the condition false. All rows subsequently fail to satisfy the condition, so no

rows are returned.

因为rownum是根据查询的结果集来对记录进行编号,所以当你查询rownum大于2的记录时会得到一个空的结果集。因为当oracle查询得到第1条记录时,发现rownum为1不满足条件,然后就继续查询第2条记录,但此时第2条记录又被编号为1(也即rownum变为1),所以查询得到的始终是rownum=1,因此无法满足约束,最终查询的结果集为空。

 

[2] rownum的排序查询问题

Rownum的排序查询是根据表中数据的初始顺序来进行的。Oracle官方文档中说明如下:

If an ORDER BY clause follows ROWNUM in the same query, then the rows will be

reordered by the ORDER BY clause. The results can vary depending on the way the

rows are accessed. For example, if the ORDER BY clause causes Oracle to use an index

to access the data, then Oracle may retrieve the rows in a different order than without

the index.

例如:select * from area where rownum <= 8 order by district;

其结果如下图所示: 

图片 11

发现没有,它只对area表中的前8条记录进行排序。那么,如果我要取表中的前8条记录并且要求是全表有序,那怎么办呢?还是老办法,使用子查询。我们可以使用以下语句得到:

select * from (select * from area order by district)

where rownum <= 8;

查询的结果如下图所示:

 


1: 假如判定件是常量,则:
只能 rownum = 1, <= 大于1 的自然数, = 大于1 的数是没有结果的;大于一个数也是没有结果的
当出现一个 rownum 不满足件的时候则 查询结束 this is stop key(一个不满足,系统将该记录过滤掉,则下一条记录的rownum还是这个,所以后面的就不再有满足记录,this is stop key)

件是 = var , 则只有当 var 为1 的时候才满足件,这个时候不存在 stop key ,必须进行full scan ,对每个满足其他where件的数据进行判定,选出一行后才能去选rownum=2的行……



(3)rownum对于小于某值的查询条rownum对于rownum<n((n>1的自然数)的件认为是成立的,所以可以找到记录。
SQL> select rownum,id,name from student where rownum <3;
    ROWNUM ID     NAME

select * from tablename
where …
and rownum<10
minus
select * from tablename
where …
and rownum<5
order by name
选出结果后用name排序显示结果。(先选再排序)


(2)rownum对于大于某值的查询条   如果想找到从第二行记录以后的记录,当使用rownum>2是查不出记录的,原因是由于rownum是一个总是从1开始的伪列,Oracle 认为rownum> n(n>1的自然数)这种件依旧不成立,所以查不到记录。

查找到第二行以后的记录可使用以下的查询方法来解决。注意子查询中的rownum必须要有别名,否则还是不会查出记录来,这是因为rownum不是某个表的列,如果不起别名的话,无法知道rownum是子查询的列还是主查询的列
SQL>select * from(select rownum no ,id,name from student) where no>2;
        NO ID     NAME

注意:只能用以上符号(<、<=、!=)。

(1) rownum 对于等于某值的查询**条
如果希望找到学生表中第一
学生的信息,可以使用rownum=1作为件。但是想找到学生表中第二学生的信息,使用rownum=2结果查不到数据。因为rownum都是从1开始,但是1以上的自然数在rownum做等于判断是时认为都是false件,所以无法查到rownum = n(n>1的自然数)。
SQL> select rownum,id,name from student where rownum=1;(可以用在限制返回记录
条**数的地方,保证不出错,如:隐式游标)
SQL> select rownum,id,name from student where rownum =2;
    ROWNUM ID     NAME


2: 假如判定值不是常量,则:

(4)rownum和排序  
Oracle中的rownum的是在取数据的时候产生的序号,所以想对指定排序的数据去指定的rowmun行数据就必须注意了。 SQL> select rownum ,id,name from student order by name;
    ROWNUM ID     NAME

另外,这个方法更快:

select * from (
select rownum r,a from yourtable
where rownum <= 20
order by name )
where r > 10
这样取出第11-20条记录!(先选再排序再选)

         3 200003 李三
         2 200002 王二
         1 200001 张一
         4 200004 赵四
可以看出,rownum并不是按照name列来生成的序号。系统是按照记录插入时的顺序给记录排的号,rowid也是顺序分配的。为了解决这个问题,必须使用子查询SQL> select rownum ,id,name from (select * from student order by name);
    ROWNUM ID     NAME

        1 200001 张一
        2 200002 王二

select * from tablename where rownum != 10;返回的是前9条记录
不能用:>,>=,=,Between...and。由于rownum是一个总是从1开始的伪列,Oracle 认为这种件不成立。

取得某列中第N大的行

select column_name from
(select table_name.*,dense_rank() over (order by column desc) rank from table_name)
where rank = &N;
 假如要返回前5条记录

         1 200003 李三
         2 200002 王二
         3 200001 张一
         4 200004 赵四
这样就成了按name排序,并且用rownum标出正确序号(有小到大)
笔者在工作中有一上百万条记录的表,在jsp页面中需对该表进行分页显示,便考虑用rownum来作,下面是具体方法(每页显示20):
“select * from tabname where rownum<20 order by name" 但却发现oracle却不能按自己的意愿来执行,而是先随便取20条记录,然后再order by,后经咨询oracle,说rownum确实就这样,想用的话,只能用子查询来实现先排序,后rownum,方法如下:
"select * from (select * from tabname order by name) where rownum<20",但这样一来,效率会低很多。
后经笔者试验,只需在order by 的字段上加主键或索引即可让oracle先按该字段排序,然后再rownum;方法不变:    “select * from tabname where rownum<20 order by name"

         2 200002 王二
         3 200003 李三

  select * from tablename where rownum<6;(或是rownum <= 5 或是rownum != 6)
假如要返回第5-9条记录

本文由9159.com发布于www.9159.com,转载请注明出处:第二行是2,我们可以采用limit语句进行分页

关键词: