在 SQL Server 2005 中使用 ROW_NUMBER() OVER () 对不同列进行排序的分页查询 [英] Paginated query using sorting on different columns using ROW_NUMBER() OVER () in SQL Server 2005

问题描述

假设我正在使用 Northwind 数据库，我想通过存储过程运行查询，其中包含以下参数:

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:

• @Offset 表示分页开始的位置，
• @Limit 表示页面大小，
• @SortColumn 表示用于排序的列，
• @SortDirection，表示升序或降序排序.

• @Offset to indicate where the pagination starts,
• @Limit to indicate the page size,
• @SortColumn to indicate the column used for sorting purposes,
• @SortDirection, to indicate ascendant or descendant sorting.

我们的想法是在数据库上进行分页，因为结果集包含数千行，所以缓存不是一个选项(并且使用 VIEWSTATE 甚至不被认为是，IMO，很糟糕).

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).

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

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 不是一个选项，所以我们有两种可能性:使用大量的 IF ... ELSE ... 和有10 个查询，这是一个很难维护的查询，或者像下面这样的查询:

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:

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?

推荐答案

简单:

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

如果选择 ASC 顺序，这将按 NULL (DESC) 排序，反之亦然.

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

让 ROW_NUMBER() 函数处理同一个 ORDER BY 表达式.

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

这篇关于在 SQL Server 2005 中使用 ROW_NUMBER() OVER () 对不同列进行排序的分页查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！