T-sql中的三种分页查询

 1 USE [APS_Future_FT]
 2 GO
 3 /****** Object:  StoredProcedure [dbo].[A_PagingAndSorting]    Script Date: 2013/11/7 21:42:16 ******/
 4 SET ANSI_NULLS ON
 5 GO
 6 SET QUOTED_IDENTIFIER ON
 7 GO
 8 -- =============================================
 9 -- Author:        <Author,,Name>
10 -- Create date: <Create Date,,>
11 -- Description:    <Description,数据库分页查询,>
12 -- =============================================
13 ALTER PROCEDURE [dbo].[A_PagingAndSorting]
14     (
15       @PageSize INT ,
16       @PageIndex INT   --从1开始
17     )
18 AS 
19     BEGIN
20     -- SET NOCOUNT ON added to prevent extra result sets from
21     -- interfering with SELECT statements.
22         SET NOCOUNT ON;
23 
24 ----------------------------------------------第一种方法：借助row_number()函数-------------------------------
25         SELECT  A.ID ,
26                 A.NAME ,
27                 A.Age
28         FROM    ( SELECT    ROW_NUMBER() OVER ( ORDER BY t.ID ASC ) AS RowNumber ,
29                             t.ID ,
30                             t.NAME ,
31                             t.Age
32                   FROM      dbo.Test t
33                 ) AS A
34         WHERE   A.RowNumber BETWEEN ( @PageIndex - 1 ) * @PageSize + 1
35                             AND     @PageIndex * @PageSize
36 
37 --------------------------------------------第二中方法：借助表变量-------------------------------------
38         DECLARE @T TABLE
39             (
40               ID INT IDENTITY(1, 1) ,
41               nid INT
42             )   
43 
44         INSERT  @T
45                 ( nid )
46                 SELECT  Te.ID
47                 FROM    dbo.Test Te
48 
49 
50         SELECT  Te.ID ,
51                 Te.NAME ,
52                 Te.Age
53         FROM    @T T
54                 INNER JOIN dbo.Test Te ON Te.ID = T.nid
55         WHERE   T.ID BETWEEN ( @PageIndex - 1 ) * @PageSize + 1
56                      AND     @PageIndex * @PageSize
57 
58 --------------------------------------------第三种方法：top---------------------------------------
59         SELECT  *
60         FROM    ( SELECT TOP ( @PageSize )
61                             *
62                   FROM      ( SELECT TOP ( @PageIndex * @PageSize )
63                                         *
64                               FROM      dbo.Test A
65                               ORDER BY  A.ID ASC
66                             ) B
67                   ORDER BY  B.ID DESC
68                 ) C
69         ORDER BY c.ID 
70     END