【SQL】Oracle分页查询的三种方法

【SQL】Oracle分页查询的三种方法

采用伪列 rownum
查询前10条记录
1
2
3
4
5
6
7
8
9
10
11
[sql]
select * from t_user t where ROWNUM <10; 
按照学生ID排名,抓取前三条记录
[java]
SELECT * FROM(SELECT id,realname FROM T_USER ORDER BY id asc ) WHERE ROWNUM <=3 
分页SQL写法,从第10条记录开始,提取10条记录。
[java]
SELECT * FROM (SELECT ROWNUM rn,id,realname FROM (SELECT id,realname FROM T_USER)WHERE ROWNUM<=20) t2 WHERE T2.rn >=10; 
按照学生ID排名,从第10条记录开始,提取10条记录。
[sql]
SELECT * FROM (SELECT ROWNUM rn,id,realname FROM (SELECT id,realname FROM T_USER ORDER BY id asc)WHERE ROWNUM<=20) t2 WHERE T2.rn >=10;
【注】
1.
1
where rownum>1

不能抓取到记录。

2.
1
where rownum between 2 and 10

也不能抓取到记录。

运用分析函数
用分析函数row_number()over(ORDER BY 字段)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
按照学生ID排名,抓取前三条记录
[sql]
SELECT * FROM(SELECT id,realname,row_number()over(ORDER BY id asc) rn FROM T_USER)WHERE rn <=3 
按照学生ID排名,从第10条记录开始,提取10条记录。
[sql]
SELECT * FROM(SELECT id,realname,row_number()over(ORDER BY id asc) rn FROM T_USER)WHERE rn BETWEEN 10 AND 20 
 
运用minus方法
从第10条记录开始,提取10条记录。
[java]
SELECT * FROM T_USER WHERE ROWNUM<20 MINUS SELECT * FROM T_USER WHERE ROWNUM<10; 
按ID排序后,从第10条记录开始,提取10条记录。
[sql]
(SELECT * FROM (SELECT * FROM T_USER ORDER BY id asc) WHERE ROWNUM<20)  MINUS( SELECT * FROM (SELECT * FROM T_USER ORDER BY id asc) WHERE ROWNUM<10);

sql = " SELECT * FROM ( SELECT A.*,rownum rn FROM ( SELECT * FROM  task ) A WHERE rownum <=" + (pageIndex) * pageSize + " ) WHERE rn>" + (pageIndex - 1) * pageSize + " ORDER BY ADDTIME DESC,OBJECTID DESC";

上一篇:forget word out a~2


下一篇:基于socket网络编程技术实现TCP和UDP的流程详解及实例