一个通用的分页查询语句

今天没事翻翻书，看到这条查询语句，感觉思路简单，用起来也方便，就记下了，哈哈，虽然在业务层里或者数据层中实现分页也都可行，还是拿sql方便点

select top pagesize *
2from 表
3where 条件 and id not in 
4(select top pagesize * (currentpageindex-1) id
5 from 表
6 where 条件 order by 排序条件
7)
8

简单的sql，仔细想想就明白了
发现sqlserver2005中，增加了新的函数row_numer()可以返回行号，则可以利用此函数来进行分页

 

ROW_NUMBER() OVER (order by orderDate)as RowNumber  

 

-------------------

USE AdventureWorks;
2GO 
3Select SalesOrderID, orderDate,
4ROW_NUMBER() OVER (order by orderDate)as RowNumber
5FROM Sales.SalesOrderHeader
6GO
7

 

功能：在返回的记录集中新增加一计数列，
ROW_NUMBER() OVER (order by orderDate)as RowNumber 以OrderDate的从小到大的顺序(顺序可以改)排序
RowNumber 按照排序结果顺序从1开始记数编号

结果如下:

SalesOrderID orderDate RowNumber
--------- ---------------- ---------
43659 2001-07-01 00:00:00.000 1
43660 2001-07-01 00:00:00.000 2
43661 2001-07-01 00:00:00.000 3
43662 2001-07-01 00:00:00.000 4
43663 2001-07-01 00:00:00.000 5
43664 2001-07-01 00:00:00.000 6
43665 2001-07-01 00:00:00.000 7
43666 2001-07-01 00:00:00.000 8
43667 2001-07-01 00:00:00.000 9
43668 2001-07-01 00:00:00.000 10
43669 2001-07-01 00:00:00.000 11
43670 2001-07-01 00:00:00.000 12
43671 2001-07-01 00:00:00.000 13
43672 2001-07-01 00:00:00.000 14
43673 2001-07-01 00:00:00.000 15
43674 2001-07-01 00:00:00.000 16
43675 2001-07-01 00:00:00.000 17
43676 2001-07-01 00:00:00.000 18
43677 2001-07-01 00:00:00.000 19
43678 2001-07-01 00:00:00.000 20
43679 2001-07-01 00:00:00.000 21
43680 2001-07-01 00:00:00.000 22
43681 2001-07-01 00:00:00.000 23
43682 2001-07-01 00:00:00.000 24
43683 2001-07-01 00:00:00.000 25
43684 2001-07-01 00:00:00.000 26
43685 2001-07-01 00:00:00.000 27
43686 2001-07-01 00:00:00.000 28
43687 2001-07-01 00:00:00.000 29
43688 2001-07-01 00:00:00.000 30
43689 2001-07-01 00:00:00.000 31
43690 2001-07-01 00:00:00.000 32
43691 2001-07-01 00:00:00.000 33
43692 2001-07-01 00:00:00.000 34
43693 2001-07-01 00:00:00.000 35