Oracle的rownum及分页
程序里常常会遇到分页的问题,因为用hibernate,所以一直就是用setMaxResult(),setFirstResult()之类的来搞定,可万一不让用hibernate怎么办?直接用SQL怎么搞定呢?
解析oracle的rownum
对于rownum来说它是oracle系统顺序分配为从查询返回的行的编号,返回的第一行分配的是1,第二行是2,依此类推,这个伪字段可以用于限制查询返回的总行数,而且rownum不能以任何表的名称作为前缀。
举例说明:
例如表:student(学生)表,表结构为:
sql 代
码
1. ID char(6) --学号
2. name VARCHAR2(10) --姓名
3. create table student (ID char(6), name VARCHAR2(100));
4. insert into sale values('200001',‘张一’);
5. insert into sale values('200002',‘王二’);
6. insert into sale values('200003',‘李三’);
7. nsert into sale values('200004',‘赵四’);
8. commit;
1. ID char(6) --学号
2. name VARCHAR2(10) --姓名
3. create table student (ID char(6), name VARCHAR2(100));
4. insert into sale values('200001',‘张一’);
5. insert into sale values('200002',‘王二’);
6. insert into sale values('200003',‘李三’);
7. nsert into sale values('200004',‘赵四’);
8. commit;
如果希望找到学生表中第一条学生的信息,可以使用rownum=1作为条件。但是想找到学生表中第二条学生的信息,使用rownum=2结果查不到数据。 因为rownum都是从1开始,但是1以上的自然数在rownum做等于判断是时认为都是false条件,所以无法查到rownum = n(n>1的自然数)。
sql 代码
1. SQL> select rownum,id,name from student where rownum=1;(可以用在限制返回记录条数的地方,保证不出错,如:隐式游标)
2. SQL> select rownum,id,name from student where rownum=1;
3. ROWNUM ID NAME
4. ---------- ------ ---------------------------------------------------
5. 1 200001 张一
6. SQL> select rownum,id,name from student where rownum =2;
7. ROWNUM ID NAME
8. ---------- ------ ---------------------------------------------------
1. SQL> select rownum,id,name from student where rownum=1;(可以用在限制返回记录条数的地方,保证不出错,如:隐式游标)
2. SQL> select rownum,id,name from student where rownum=1;
3. ROWNUM ID NAME
4. ---------- ------ ---------------------------------------------------
5. 1 200001 张一
6. SQL> select rownum,id,name from student where rownum =2;
7. ROWNUM ID NAME
8. ---------- ------ ---------------------------------------------------
如果想找到从第二行记录以后的记录,当使用rownum>2是查不出记录的,原因是由于rownum是一个总是从1开始的伪列,Oracle 认为rownum> n(n>1的自然数)这种条件依旧不成立,所以查不到记录。
sql 代码
1. SQL> select rownum,id,name from student where rownum >2;
2. ROWNUM ID NAME
3. ---------- ------ ---------------------------------------------------
1. SQL> select rownum,id,name from student where rownum >2;
2. ROWNUM ID NAME
3. ---------- ------ ---------------------------------------------------
sql 代码
1. SQL>select * from(select rownum no ,id,name from student) where no>2;
2. NO ID NAME
3. ---------- ------ ---------------------------------------------------
4. 3 200003 李三
5. 4 200004 赵四
6. SQL> select * from(select rownum,id,name from student)where rownum>2;
7. ROWNUM ID NAME
8. ---------- ------ ---------------------------------------------------
1. SQL>select * from(select rownum no ,id,name from student) where no>2;
2. NO ID NAME
3. ---------- ------ ---------------------------------------------------
4. 3 200003 李三
5. 4 200004 赵四
6. SQL> select * from(select rownum,id,name from student)where rownum>2;
7. ROWNUM ID NAME
8. ---------- ------ ---------------------------------------------------
如果想找到第三条记录以前的记录,当使用rownum<3是能得到两条记录的。显然rownum对于rownum<n((n>1的自然数)的条件认为是成立的,所以可以找到记录。
sql 代码
1 . SQL > select rownum,id,name from student where rownum < 3 ;
2 . ROWNUM ID NAME
3 . -- -------- ------ ---------------------------------------------------
4 . 1 200001 张一
5 . 2 200002 王二
1 . SQL > select rownum,id,name from student where rownum < 3 ;
2 . ROWNUM ID NAME
3 . -- -------- ------ ---------------------------------------------------
4 . 1 200001 张一
5 . 2 200002 王二
sql 代码
1 . SQL > select * from ( select rownum no,id,name from student where rownum <= 3 ) where no >= 2 ;
2 . NO ID NAME
3 . -- -------- ------ ---------------------------------------------------
4 . 2 200002 王二
5 . 3 200003 李三
1 . SQL > select * from ( select rownum no,id,name from student where rownum <= 3 ) where no >= 2 ;
2 . NO ID NAME
3 . -- -------- ------ ---------------------------------------------------
4 . 2 200002 王二
5 . 3 200003 李三
Oracle中的rownum的是在取数据的时候产生的序号,所以想对指定排序的数据去指定的rowmun行数据就必须注意了。
sql 代码
1 . SQL > select rownum ,id,name from student order by name;
2 . ROWNUM ID NAME
3 . -- -------- ------ ---------------------------------------------------
4 . 3 200003 李三
5 . 2 200002 王二
6 . 1 200001 张一
7 . 4 200004 赵四
1 . SQL > select rownum ,id,name from student order by name;
2 . ROWNUM ID NAME
3 . -- -------- ------ ---------------------------------------------------
4 . 3 200003 李三
5 . 2 200002 王二
6 . 1 200001 张一
7 . 4 200004 赵四
sql 代码
1 . SQL > select rownum ,id,name from ( select * from student order by name);
2 . ROWNUM ID NAME
3 . -- -------- ------ ---------------------------------------------------
4 . 1 200003 李三
5 . 2 200002 王二
6 . 3 200001 张一
7 . 4 200004 赵四
1 . SQL > select rownum ,id,name from ( select * from student order by name);
2 . ROWNUM ID NAME
3 . -- -------- ------ ---------------------------------------------------
4 . 1 200003 李三
5 . 2 200002 王二
6 . 3 200001 张一
7 . 4 200004 赵四
这样就成了按name排序,并且用rownum标出正确序号(有小到大)
一个例子
取出100-150条记录,
sql 代码
1 . select tname,tabtype from (
2 . select tname,tabtype,rownum rn from tab where rownum <= 150
3 . )
4 . where rn >= 100 ;
1 . select tname,tabtype from (
2 . select tname,tabtype,rownum rn from tab where rownum <= 150
3 . )
4 . where rn >= 100 ;
sql 代码
1 . select tname,tabtype from ( select tname,tabtype,rownum rn from ( select * from tab order by tname)
where rownum <= 150 ) where rn >= 100 ;
1 . select tname,tabtype from ( select tname,tabtype,rownum rn from ( select * from tab order by tname)
where rownum <= 150 ) where rn >= 100 ;
Mysql中limit的用法详解
SELECT * FROM table LIMIT [offset,] rows | rows OFFSET offset
LIMIT 子句可以被用于强制 SELECT 语句返回指定的记录数。LIMIT 接受一个或两个数字参数。参数必须是一个整数常量。如果给定两个参数,第一个参数指定第一个返回记录行的偏移量,第二个参数指定返回记录行的最大数目。初始记录行的偏移量是 0(而不是 1): 为了与 PostgreSQL 兼容,MySQL 也支持句法: LIMIT # OFFSET #。f
mysql
>
SELECT
FROM
table
LIMIT
5
,
10
;
//
检索记录行
6
-
15
// 为了检索从某一个偏移量到记录集的结束所有的记录行,可以指定第二个参数为 - 1 :
mysql > SELECT FROM table LIMIT 95 , - 1 ; // 检索记录行 96 - last.
// 如果只给定一个参数,它表示返回最大的记录行数目:
mysql > SELECT * FROM table LIMIT 5 ; // 检索前 5 个记录行
// 换句话说,LIMIT n 等价于 LIMIT 0 ,n。
// 为了检索从某一个偏移量到记录集的结束所有的记录行,可以指定第二个参数为 - 1 :
mysql > SELECT FROM table LIMIT 95 , - 1 ; // 检索记录行 96 - last.
// 如果只给定一个参数,它表示返回最大的记录行数目:
mysql > SELECT * FROM table LIMIT 5 ; // 检索前 5 个记录行
// 换句话说,LIMIT n 等价于 LIMIT 0 ,n。
Sql-Server 2k
建立表:
CREATE TABLE [TestTable] (
[ID] [int] IDENTITY (1, 1) NOT NULL ,
[FirstName] [nvarchar] (100) COLLATE Chinese_PRC_CI_AS NULL ,
[LastName] [nvarchar] (100) COLLATE Chinese_PRC_CI_AS NULL ,
[Country] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[Note] [nvarchar] (2000) COLLATE Chinese_PRC_CI_AS NULL
) ON [PRIMARY]
GO
[ID] [int] IDENTITY (1, 1) NOT NULL ,
[FirstName] [nvarchar] (100) COLLATE Chinese_PRC_CI_AS NULL ,
[LastName] [nvarchar] (100) COLLATE Chinese_PRC_CI_AS NULL ,
[Country] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[Note] [nvarchar] (2000) COLLATE Chinese_PRC_CI_AS NULL
) ON [PRIMARY]
GO
SET
IDENTITY_INSERT
TestTable
ON
declare @i int
set @i = 1
while @i <= 20000
begin
insert into TestTable( [ id ] , FirstName, LastName, Country,Note) values ( @i , '' FirstName_XXX '' , '' LastName_XXX '' , '' Country_XXX '' , '' Note_XXX '' )
set @i = @i + 1
end
SET IDENTITY_INSERT TestTable OFF
declare @i int
set @i = 1
while @i <= 20000
begin
insert into TestTable( [ id ] , FirstName, LastName, Country,Note) values ( @i , '' FirstName_XXX '' , '' LastName_XXX '' , '' Country_XXX '' , '' Note_XXX '' )
set @i = @i + 1
end
SET IDENTITY_INSERT TestTable OFF
分页方案一:(利用Not In和SELECT TOP分页)
语句形式:
SELECT TOP 10 * FROM TestTable
WHERE ( ID NOT IN
( SELECT TOP 20 id FROM TestTable ORDER BY id))
ORDER BY ID
SELECT TOP 页大小 * FROM TestTable
WHERE (ID NOT IN
( SELECT TOP 页大小 * 页数 id FROM 表 ORDER BY id))
ORDER BY ID
SELECT TOP 10 * FROM TestTable
WHERE ( ID NOT IN
( SELECT TOP 20 id FROM TestTable ORDER BY id))
ORDER BY ID
SELECT TOP 页大小 * FROM TestTable
WHERE (ID NOT IN
( SELECT TOP 页大小 * 页数 id FROM 表 ORDER BY id))
ORDER BY ID
分页方案二:(利用ID大于多少和SELECT TOP分页)
语句形式:
SELECT TOP 10 * FROM TestTable
WHERE (ID >
( SELECT MAX (id) FROM ( SELECT TOP 20 id FROM TestTable ORDER BY id) AS T))
ORDER BY ID
SELECT TOP 页大小 * FROM TestTable
WHERE (ID >
( SELECT MAX (id) FROM ( SELECT TOP 页大小 * 页数 id FROM 表 ORDER BY id) AS T))
ORDER BY ID
SELECT TOP 10 * FROM TestTable
WHERE (ID >
( SELECT MAX (id) FROM ( SELECT TOP 20 id FROM TestTable ORDER BY id) AS T))
ORDER BY ID
SELECT TOP 页大小 * FROM TestTable
WHERE (ID >
( SELECT MAX (id) FROM ( SELECT TOP 页大小 * 页数 id FROM 表 ORDER BY id) AS T))
ORDER BY ID
分页方案三:(利用SQL的游标存储过程分页)
create
procedure
XiaoZhengGe
@sqlstr nvarchar ( 4000 ), -- 查询字符串
@currentpage int , -- 第N页
@pagesize int -- 每页行数
as
set nocount on
declare @P1 int , -- P1是游标的id
@rowcount int
exec sp_cursoropen @P1 output, @sqlstr , @scrollopt = 1 , @ccopt = 1 , @rowcount = @rowcount output
select ceiling ( 1.0 * @rowcount / @pagesize ) as 总页数 -- ,@rowcount as 总行数,@currentpage as 当前页
set @currentpage = ( @currentpage - 1 ) * @pagesize + 1
exec sp_cursorfetch @P1 , 16 , @currentpage , @pagesize
exec sp_cursorclose @P1
set nocount off
@sqlstr nvarchar ( 4000 ), -- 查询字符串
@currentpage int , -- 第N页
@pagesize int -- 每页行数
as
set nocount on
declare @P1 int , -- P1是游标的id
@rowcount int
exec sp_cursoropen @P1 output, @sqlstr , @scrollopt = 1 , @ccopt = 1 , @rowcount = @rowcount output
select ceiling ( 1.0 * @rowcount / @pagesize ) as 总页数 -- ,@rowcount as 总行数,@currentpage as 当前页
set @currentpage = ( @currentpage - 1 ) * @pagesize + 1
exec sp_cursorfetch @P1 , 16 , @currentpage , @pagesize
exec sp_cursorclose @P1
set nocount off
其它的方案:如果没有主键,可以用临时表,也可以用方案三做,但是效率会低。
建议优化的时候,加上主键和索引,查询效率会提高。
通过SQL 查询分析器,显示比较:我的结论是:
分页方案二:(利用ID大于多少和SELECT TOP分页)效率最高,需要拼接SQL语句
分页方案一:(利用Not In和SELECT TOP分页) 效率次之,需要拼接SQL语句
分页方案三:(利用SQL的游标存储过程分页) 效率最差,但是最为通用
在实际情况中,要具体分析。
建议优化的时候,加上主键和索引,查询效率会提高。
通过SQL 查询分析器,显示比较:我的结论是:
分页方案二:(利用ID大于多少和SELECT TOP分页)效率最高,需要拼接SQL语句
分页方案一:(利用Not In和SELECT TOP分页) 效率次之,需要拼接SQL语句
分页方案三:(利用SQL的游标存储过程分页) 效率最差,但是最为通用
在实际情况中,要具体分析。
Sql-Server 2005
SELECT *
FROM (select ROW_NUMBER() Over(order by CreditCardID) as rowNum, *
from Sales.CreditCard) as table
where rowNum> 0 and rowNum<50
FROM (select ROW_NUMBER() Over(order by CreditCardID) as rowNum, *
from Sales.CreditCard) as table
where rowNum> 0 and rowNum<50