sqlserver 的3种分页方式..

---sqlServer 2005 分页语句集合
----缺点：
 -- top：必须用户编写复杂sql,不支持复合主键
 -- max：必须用户编写复杂sql，不支持非唯一列排序 
 --row：不支持sqlserver2000
--------------------------------
select TOP @pagesize id,email,qq,wechart,phone,phone1 FROM contact
where id not in
(SELECT TOP (@pagesize*(currentpage-1)) id from contact ORDER BY id ASC )
ORDER BY id ASC


-- 效率最低下的分页语句（只需要知道页数和每页的显示数目即可）
select * from  AISINO_BD_TelephoneRecord order by ID asc;
select top 5 ID,companyID,projectID from dbo.AISINO_BD_TelephoneRecord
where ID not in(
select top (5*(2-1)) ID  from  AISINO_BD_TelephoneRecord order by ID asc
) order by ID asc


----------------------第二种方法，只需要知道页数和每页的显示数目即可-------------------------------
 
select top 5 * from AISINO_BD_TelephoneRecord
where ID>(
select max(ID)
from (select top (5*(2-1)) ID from AISINO_BD_TelephoneRecord order by ID asc)tt
) 
order by ID asc


-------------------第三种方法---只需要知道页数和每页的显示数目即可----------------------------------


select *
from (select top (5*(1-1)+5)  row_number()over(order by Id asc)__rn__,* from AISINO_BD_TelephoneRecord)t
where __rn__>5*(1-1)

在oracle 中的分页:

oracle 自带了rownum,直接使用rownum 进行分页：

select *
  from (select a.*,rownum nm
          from (select * from aos_rms_user) a
         where rownum <= 5*(1-1)+5)
 where nm >= 5*(1-1)

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!