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

想法是在数据库上进行分页，因为结果集包含成千上万的行，所以缓存不是一个选择(使用IMO甚至都不认为使用VIEWSTATE).

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.

我们需要对每个返回的列进行排序(在此示例中为5)，并且不选择动态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屋！