SQL server:
设置几个分页变量:
pageSize //每页的记录数 pageNow //当前所在页
问题:按照singerId 排序,获取第11-15条记录。
第一种方案:
使用select top 和 not in
select top 5 * from singerId where singerid not in (select top 10 singerid from singer order by singerid)
go
第二种方案:
利用id大于多少和select top分页语句:
select top 5 * from singer
where singerid > (select MAX(singerid) from
(select top 10 singerid from singer order by singerid) As t)
order by singerid
go
第三种方案
利用特性 ROW_NUMBER进行分页:
select * from (
select ROW_NUMBER() OVER (order by singerid) AS s,*
from singer
) AS mytable
where s between 11 and 15
go
随机返回N条数据:
top 后面的N是5,代表随机返回5条数据
select top 5 * from singer order by NEWID()
go
MySQL的分页:
创建一个表:
create table student(
sid int primary key, –编号
sname varchar(20) not null, –姓名
sage int not null –年龄
);
问题:按sid由小到大排序,找出第3条到第8条的记录。
方案:
select * from student order by sid limit 2 (offset)3
注意:limit后面的两个数,分别表示从那条记录算起,查找几条记录,MySQL中记录是从第0条开始算的。
select * from student order by sid limit pageSize*(pageNow -1),pageSize;
语法:
limit pageSize offset pageSize*(pageNum - 1);
Oracle的分页:
简单的分页:
rownum后面的符号,只能是 <,<=。
不能是 >,>=,=。
select rownum,s.* from song s where rownum <= 10 //查询前10条数据
select * from (select rownum r,s.* from song s) where r > 10 and r <= 20
//查询11-20条数据
基于排序查询:
select * from (select rownum r,s.* from (select * from song s order by songid desc ) t )
where r > 10 and r <= 20