SQL SERVER 三种常用分页技术

自己记录三种常用的分页技术：

　介绍之前呢先说一下分页俩个参数：显示记录的页数(pageIndex)和每一页有多少条记录(pageSize)

一个简单的算法：我们假如每一页10条数据，pageSize = 10，显示第一页就是从第1条记录开始，第10条记录结束

第二页：11———————————>20

第三页：21  ——————————>30  

那么第pageIndex页的数据就是：(pageIndex - 1) * pageSize ————> (pageIndex - 1) * pageSize + pageSize

建立一个表作为测试：

/****** 对象:  Table [dbo].[Store]    脚本日期: 07/22/2013 17:25:15 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Store](
    [id] [int] IDENTITY(1,1) NOT NULL,
    [did] [varchar](50) COLLATE Chinese_PRC_CI_AS NOT NULL,
    [dname] [varchar](50) COLLATE Chinese_PRC_CI_AS NOT NULL,
    [pid] [varchar](50) COLLATE Chinese_PRC_CI_AS NULL,
 CONSTRAINT [PK_Store] PRIMARY KEY CLUSTERED 
(
    [id] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF

　

下面记录分页方法：

1. 利用TOP关键字
   TOP就是选择固定的条数，例如：

   SELECT TOP 10 * FROM store;

   就是选择person表中的最前面的10条数据。
   我们选择第pageIndex页的pageSize条数据分两步走：
   　　首先，选择把前(pageIndex - 1)页的数据选择出来，SQL为：

   SELECT TOP ((pageIndex - 1)*pageSize) id FROM store ORDER BY id

   　　其次：出去前面的数据，选择余下数据的前pageSize条

   SELECT TOP pageSize * FROM store WHERE id NOT IN
        (SELECT TOP ((pageIndex - 1)*pageSize) id FROM store ORDER BY id)
   ORDER BY id

2. 利用MAX
   与前一个相似，就是第二步的时候换一下

   SELECT TOP pageSize * FROM store WHERE id > 
   　　(SELECT MAX(id) FROM 
   　　　　(SELECT TOP ((pageIndex - 1)*pageSize) id FROM store ORDER BY id) as t) 
   ORDER BY id

   不知道大家发现一个问题没有，就是当pageIndex = 1时，内层的SQL语句就变成了

   SELECT TOP 0 id FROM store ORDER BY id

   然后再利用MAX(id)是没有值的，那么显示第一页时，是不出现任何记录的，所以改进一下，当MAX选择的值是空时，转换成0

   SELECT TOP pageSize * FROM store WHERE id > 
   　　ISNULL((SELECT MAX(id) FROM 
   　　　　(SELECT TOP ((pageIndex - 1)*pageSize) id FROM store ORDER BY id) as t) ,0)
   ORDER BY id

   这样就OK了...

3. 利用row_number()函数
   
   最简单的我们直接可以这样写

   SELECT * FROM (
   　　SELECT row_number() OVER(ORDER BY id) tempRow,* FROM store )t 
   WHERE tem BETWEEN (pageIndex-1)*pageSize+1 AND  pageIndex*pageSize

   但是这样也有一个小问题就是我必须知道表中的一个列，例如现在的id，就是over(order by id),现在我期望写的存储过程只关注pageIndex和pageSize两个参数：则可以利用一个不知道的列temp_column
   首先，利用

   SELECT temp_column=0,* FROM store

   相当于在store表中添加了一个列temp_column,再利用

   SELECT row_number() OVER(ORDER BY temp_column) tempColumnNumber,* FORM (SELECT temp_column=0,* FROM store) t

   将利用temp_column标号，并将标号的列一个别名：tempColumnNumber，最后就是选择要显示的记录了

   SELECT * FROM (
   　　SELECT row_number() OVER(ORDER BY temp_column) tempColumnNumber,* FORM (
   　　　　SELECT temp_column=0,* FROM store) t) tt
   WHERE tempColumnNumber BETWEEN (pageIndex-1)*pageSize+1 AND pageIndex*pageSize

好了，就到了里了.....

转载于:https://www.cnblogs.com/A-Iverson/p/3205820.html