SQL之查询某几行记录--分页查询

来源：互联网发布：java系统性能监控编辑：程序博客网时间：2024/05/17 04:00

ORACLE:

1、查询前10行：select * from sc_objects where rownum <=10

2、利用minus

查询10到20行：select * from sc_objects where rownum <=20 minus select * from sc_objects where rownum <=10;

查询20行之后：select * from sc_objects minus select * from sc_objects where rownum <=20;
3、常用于分页查询：

PAGESIZE：每页显示的记录数
CURRENTPAGE：当前页号
数据表的名字是：components
索引主键字是：id
select * from components where id not in(select id from components where rownum<=(PAGESIZE*(CURRENTPAGE-1))) and rownum<=PAGESIZE order by id;
如下例：
select * from sc_objects where id not in(select id from sc_objects where rownum<=100) and rownum<=10 order by id;

4、一种是利用Oracle的rownum，这个是Oracle查询自动返回的序号，一般不显示，但是可以通过select rownum from [表名]，可以看到，是从1到当前的记录总数。
select * from (select rownum tid,components.* from components where rownum<=100) where tid<=10;

MYSQL和sqLite：

查询n+1到m行： select * from tadd order by id limit n,m;

示例：

select * from tadd order by id limit 4 offset 2;
select * from tadd order by id limit 2,4;

SQLSERVER:

select top 5 * from sc_objects where id not in(select top(当前页-1)*5) id from sc_objects;

http://blog.163.com/zsclei@126/blog/static/3192610020082118334267/

引申between and 的用法

http://blog.sina.com.cn/s/blog_8ce459cd0101meyp.html

0 0