代码之家 › 专栏 › 技术社区 › Leandro López

在SQL Server 2005中,使用行号()over()对不同列进行排序的分页查询

pagination sql-server-2005

7

Leandro López · 技术社区 · 16 年前

假设我使用的是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, OrderID, CustomerID, EmployeeID, OrderDate, ShippedDate,
    @Offset, @Limit, @SortColumn, @SortDirection
FROM PaginatedOrders
WHERE RowNumber BETWEEN @Offset AND (@Offset + @Limit - 1)
ORDER BY RowNumber

我已经用不同的参数尝试过这个查询好几次了,它的性能实际上相当好,但是看起来它可能是以其他方式优化的。

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

1 回复 | 直到 15 年前

1

6

Tomalak 16 年前

简单的:

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

如果选择了升序,则将按空(DESC)排序,反之亦然。

让row_number()函数处理相同的order by表达式。