使用SQL Server 2005中的ROW_NUMBER（）OVER（）在不同列上进行排序的分页查询

Let's suppose I'm using the Northwind database and I would like to run a query via a stored procedure that contains, among other parameters, the following:

假设我正在使用Northwind数据库,并且我希望通过包含以下参数的存储过程运行查询:

@Offset to indicate where the pagination starts,

@Offset表示分页开始的位置,

@Limit to indicate the page size,

@Limit表示页面大小,

@SortColumn to indicate the column used for sorting purposes,

@SortColumn表示用于排序目的的列,

@SortDirection, to indicate ascendant or descendant sorting.

@SortDirection,表示上升或后代排序。

The idea is to do the pagination on the database, as the result set contains thousands of rows so caching is not an option (and using VIEWSTATE is not even considered as, IMO, sucks).

我的想法是对数据库进行分页,因为结果集包含数千行,因此缓存不是一个选项(使用VIEWSTATE甚至不被认为是IMO,很糟糕)。

As you may know SQL Server 2005 provides the function ROW_NUMBER which returns the sequential number of a row within a partition of a result set, starting at 1 for the first row in each partition.

您可能知道SQL Server 2005提供了函数ROW_NUMBER,它返回结果集的分区中行的序号,从1开始,每个分区的第一行。

We need sorting on every returned column (five in this example) and dynamic SQL is not an option, so we have two possibilities: using plenty of IF ... ELSE ... and having 10 queries, which is a hell to maintain, or having a query like the following:

我们需要对每个返回的列进行排序(本例中为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, OrderID, CustomerID, EmployeeID, OrderDate, ShippedDate,
    @Offset, @Limit, @SortColumn, @SortDirection
FROM PaginatedOrders
WHERE RowNumber BETWEEN @Offset AND (@Offset + @Limit - 1)
ORDER BY RowNumber

I've tried the query several times, with different arguments, and its performance it is quite good actually, but it stills looks like it might be optimized some other way.

我已经尝试过多次查询,使用不同的参数,并且它的性能实际上非常好,但它仍然看起来可能会以其他方式进行优化。

Is anything wrong with this query or you would do it this way? Do you propose a different approach?

这个查询有什么问题,或者你会这样做吗?你提出了不同的方法吗?

1 个解决方案

#1

Simple:

SELECT
  OrderID, CustomerID, EmployeeID, OrderDate, ShippedDate,
  @Offset, @Limit, @SortColumn, @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

This will sort on NULL (DESC) if ASC order is selected, or vice versa.

如果选择ASC顺序,则将对NULL(DESC)进行排序,反之亦然。

Let the ROW_NUMBER() function work over the same ORDER BY expression.

让ROW_NUMBER()函数在同一个ORDER BY表达式上工作。

#1