假设我正在使用Northwind数据库,并且我希望通过包含以下参数的存储过程运行查询：
> @Offset指示分页开始的位置,
> @Limit表示页面大小,
> @SortColumn表示用于排序的列,
> @SortDirection,表示上升或后代排序.
我的想法是对数据库进行分页,因为结果集包含数千行,因此缓存不是一个选项(使用VIEWSTATE甚至不被认为是IMO,很糟糕).
您可能知道sql Server 2005提供函数ROW_NUMBER,它返回结果集分区中行的序号,从1开始,每个分区的第一行.
我们需要对每个返回的列进行排序(本例中为5),动态sql不是一个选项,所以我们有两种可能：使用大量的IF … ELSE …并且有10个查询,这是一个难以维护的地方,或者有如下查询：
WITH PaginatedOrders AS (
SELECT
CASE (@SortColumn + ':' + @SortDirection)
WHEN 'OrderID:A' THEN ROW_NUMBER() OVER (ORDER BY Orders.OrderID ASC)
WHEN 'OrderID:D' THEN ROW_NUMBER() OVER (ORDER BY Orders.OrderID DESC)
WHEN 'CustomerID:A' THEN ROW_NUMBER() OVER (ORDER BY Orders.CustomerID ASC)
WHEN 'CustomerID:D' THEN ROW_NUMBER() OVER (ORDER BY Orders.CustomerID DESC)
WHEN 'EmployeeID:A' THEN ROW_NUMBER() OVER (ORDER BY Orders.EmployeeID ASC)
WHEN 'EmployeeID:D' THEN ROW_NUMBER() OVER (ORDER BY Orders.EmployeeID DESC)
WHEN 'OrderDate:A' THEN ROW_NUMBER() OVER (ORDER BY Orders.OrderDate ASC)
WHEN 'OrderDate:D' THEN ROW_NUMBER() OVER (ORDER BY Orders.OrderDate DESC)
WHEN 'ShippedDate:A' THEN ROW_NUMBER() OVER (ORDER BY Orders.OrderID ASC)
WHEN 'ShippedDate:D' THEN ROW_NUMBER() OVER (ORDER BY Orders.OrderID DESC)
END AS RowNumber,OrderID,CustomerID,EmployeeID,OrderDate,ShippedDate
FROM Orders
-- WHERE clause goes here
)
SELECT
RowNumber,ShippedDate,@Offset,@Limit,@SortColumn,@SortDirection
FROM PaginatedOrders
WHERE RowNumber BETWEEN @Offset AND (@Offset + @Limit - 1)
ORDER BY RowNumber
解决方法
简单：
SELECT
OrderID,@SortDirection
FROM
Orders
WHERE
ROW_NUMBER() OVER
(
ORDER BY
/* same expression as in the ORDER BY of the whole query */
) BETWEEN (@PageNum - 1) * @PageSize + 1 AND @PageNum * @PageSize
/* AND more conditions ... */
ORDER BY
CASE WHEN @SortDirection = 'A' THEN
CASE @SortColumn
WHEN 'OrderID' THEN OrderID
WHEN 'CustomerID' THEN CustomerID
/* more... */
END
END,CASE WHEN @SortDirection = 'D' THEN
CASE @SortColumn
WHEN 'OrderID' THEN OrderID
WHEN 'CustomerID' THEN CustomerID
/* more... */
END
END DESC
如果选择ASC顺序,则将对NULL(DESC)进行排序,反之亦然.
让ROW_NUMBER()函数在同一个ORDER BY表达式上工作.
