sqlserver的3种分页方式

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

12

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

20

21
----------------------第二种方法，只需要知道页数和每页的显示数目即可-------------------------------
22
  
23
select top 5 * from AISINO_BD_TelephoneRecord
24
where ID>





(
25
select max(ID)
26
from (select top (5*(2-1)) ID from AISINO_BD_TelephoneRecord order by ID asc)tt
27
)
28
order by ID asc
29

30

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

33

34
select *
35
from (select top (5*(1-1)+5)  row_number()over(order by Id asc)__rn__, * from AISINO_BD_TelephoneRecord)t
36
where __rn__>5*(1-1)
在oracle 中的分页:

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

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