【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";