0
0
0
1. 云栖社区>
2. 博客>
3. 正文

# 二 实现

## Top分页

```SELECT TOP (@pageSize)
*
FROM    ( SELECT TOP ( @pageSize * @pageIndex )
*
FROM      [Northwind].[dbo].[Products]
ORDER BY  UnitPrice DESC
) AS product
ORDER BY product.UnitPrice ```

```SELECT  *
FROM    dbo.Products
WHERE   ProductID IN (
SELECT TOP ( @pageSize )
ProductID
FROM    dbo.Products
WHERE   ProductID NOT IN ( SELECT TOP ( @pageSize * (@pageIndex-1) )
ProductID
FROM     dbo.Products
ORDER BY UnitPrice DESC )
ORDER BY dbo.Products.UnitPrice DESC )
ORDER BY dbo.Products.UnitPrice ASC
```

## 使用ROW_NUMBER 函数分页

```SELECT  ROW_NUMBER() OVER ( ORDER BY dbo.Products.ProductID DESC ) AS rownum ,
*
FROM    dbo.Products
```

```SELECT  ROW_NUMBER() OVER ( ORDER BY dbo.Products.ProductID DESC ) AS rownum ,
*
FROM    dbo.Products
WHERE rownum BETWEEN 1 AND 10```

```Invalid column name 'rownum'.
```

```SELECT  ROW_NUMBER() OVER ( ORDER BY dbo.Products.ProductID DESC ) AS rownum ,
*
FROM    dbo.Products
WHERE ( ROW_NUMBER() OVER (ORDER BY City) AS rown ) BETWEEN 1 AND 10```

```Incorrect syntax near the keyword 'AS'.
```

```SELECT  *
FROM    ( SELECT    ROW_NUMBER() OVER ( ORDER BY dbo.Products.ProductID DESC ) AS rownum ,
*
FROM      dbo.Products
) AS temp
WHERE   temp.rownum BETWEEN 1 AND 10```

```SELECT  *
FROM    ( SELECT TOP ( @pageSize * @pageIndex )
ROW_NUMBER() OVER ( ORDER BY dbo.Products.UnitPrice DESC ) AS rownum ,
*
FROM      dbo.Products
) AS temp
WHERE   temp.rownum > ( @pageSize * ( @pageIndex - 1 ) )
ORDER BY temp.UnitPrice
```

```WITH    ProductEntity
AS ( SELECT TOP ( @pageSize * @pageIndex )
ROW_NUMBER() OVER ( ORDER BY dbo.Products.UnitPrice DESC ) AS rownum ,
*
FROM     dbo.Products
)
SELECT  *
FROM    ProductEntity
WHERE   ProductEntity.rownum > ( @pageSize * ( @pageIndex - 1 ) )
ORDER BY ProductEntity.UnitPrice
```

```WITH    ProductEntity
AS ( SELECT TOP ( @pageSize * @pageIndex )
ROW_NUMBER() OVER ( ORDER BY dbo.Products.UnitPrice DESC ) AS rownum ,
ProductID ,--主键，
UnitPrice--待排序字段
FROM     dbo.Products
)
SELECT  *
FROM    ProductEntity
INNER JOIN dbo.Products ON dbo.Products.ProductID = ProductEntity.ProductID
WHERE   ProductEntity.rownum > ( @pageSize * ( @pageIndex - 1 ) )
ORDER BY ProductEntity.UnitPrice
```

```List<Product> product;
int pageSize = 10;
int pageIndex = 8;
using (ProductsDataContext context = new ProductsDataContext())
{
product = context.Products.OrderByDescending(x => x.UnitPrice)//排序
.Skip(pageSize * (pageIndex-1))//跳过前面的记录
.Take(pageSize)//选取每一页个数
.ToList();
}```

```EXEC sp_executesql N'SELECT [t1].[ProductID], [t1].[ProductName], [t1].[SupplierID], [t1].[CategoryID], [t1].[QuantityPerUnit], [t1].[UnitPrice], [t1].[UnitsInStock], [t1].[UnitsOnOrder], [t1].[ReorderLevel], [t1].[Discontinued]
FROM (
SELECT ROW_NUMBER() OVER (ORDER BY [t0].[UnitPrice] DESC) AS [ROW_NUMBER], [t0].[ProductID], [t0].[ProductName], [t0].[SupplierID], [t0].[CategoryID], [t0].[QuantityPerUnit], [t0].[UnitPrice], [t0].[UnitsInStock], [t0].[UnitsOnOrder], [t0].[ReorderLevel], [t0].[Discontinued]
FROM [dbo].[Products] AS [t0]
) AS [t1]
WHERE [t1].[ROW_NUMBER] BETWEEN @p0 + 1 AND @p0 + @p1
ORDER BY [t1].[ROW_NUMBER]', N'@p0 int,@p1 int', @p0 = 70, @p1 = 10```

## 使用OFFSET FETCH子句分页

```SELECT  *
FROM    dbo.Products
ORDER   BY UnitPrice DESC
OFFSET  ( @pageSize * ( @pageIndex - 1 )) ROWS
FETCH NEXT @pageSize ROWS ONLY;
```

+ 关注