sql-server-2005 – 使用SQL Server 2005中的ROW_NUMBER()OVER()在不同列上

假设我正在使用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表达式上工作.

（编辑：安卓应用网）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!