一个通用的分页查询语句

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

1 select   top  pagesize  *
2 from  表
3 where  条件  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  

 

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

1 USE  AdventureWorks;
2 GO  
3 Select  SalesOrderID, orderDate,
4 ROW_NUMBER()  OVER  ( order   by  orderDate) as  RowNumber
5 FROM  Sales.SalesOrderHeader
6 GO
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

转载于:https://www.cnblogs.com/xiziyin/archive/2009/04/15/1436325.html