sql-server – 在存储过程中分页,排序和过滤(SQL Server)

我正在寻找编写存储过程的不同方法来返回数据的“页面”.这适用于ASP ObjectDataSource,但它可以被认为是一个更普遍的问题.

要求是基于通常的寻呼参数返回数据的子集; startPageIndex和maximumRows,还有一个sortBy参数,允许对数据进行排序.还有一些参数传入以过滤各种条件下的数据.

一种常见的方法似乎是这样的：

;WITH stuff AS (
    SELECT 
        CASE 
            WHEN @SortBy = 'Name' THEN ROW_NUMBER() OVER (ORDER BY Name)
            WHEN @SortBy = 'Name DESC' THEN ROW_NUMBER() OVER (ORDER BY Name DESC)
            WHEN @SortBy = ... 
            ELSE ROW_NUMBER() OVER (ORDER BY whatever)
        END AS Row,.,FROM Table1
    INNER JOIN Table2 ...
    LEFT JOIN Table3 ...
    WHERE ... (lots of things to check)
    ) 
SELECT *
FROM stuff 
WHERE (Row > @startRowIndex)
AND   (Row <= @startRowIndex + @maximumRows OR @maximumRows <= 0)
ORDER BY Row

这样做的一个问题是它没有给出总计数,通常我们需要另一个存储过程.第二个存储过程必须复制参数列表和复杂的WHERE子句.不太好.

一种解决方案是在最终选择列表中添加一个额外的列,(SELECT COUNT(*)FROM stuff)AS TotalRows.这给了我们总数,但是对于结果集中的每一行重复它,这是不理想的.

[方法2]
这里给出了一个有趣的替代方法(http://www.4guysfromrolla.com/articles/032206-1.aspx),使用动态sql.他认为性能更好,因为第一个解决方案中的CASE语句拖延了事情.足够公平,这个解决方案可以很容易地获得totalRows并将其打成输出参数.但我讨厌动态sql编码.所有那些’sql’STR(@ parm1)’都是更多的sql’gubbins.

[方法3]
我可以找到得到我想要的唯一方法,不重复必须同步的代码,并保持合理的可读性是回到使用表变量的“旧方法”：

DECLARE @stuff TABLE (Row INT,...)

INSERT INTO @stuff
SELECT 
    CASE 
        WHEN @SortBy = 'Name' THEN ROW_NUMBER() OVER (ORDER BY Name)
        WHEN @SortBy = 'Name DESC' THEN ROW_NUMBER() OVER (ORDER BY Name DESC)
        WHEN @SortBy = ... 
        ELSE ROW_NUMBER() OVER (ORDER BY whatever)
    END AS Row,FROM Table1
INNER JOIN Table2 ...
LEFT JOIN Table3 ...
WHERE ... (lots of things to check)

SELECT *
FROM stuff 
WHERE (Row > @startRowIndex)
AND   (Row <= @startRowIndex + @maximumRows OR @maximumRows <= 0)
ORDER BY Row

(或者在表变量上使用IDENTITY列的类似方法).
在这里,我可以在表变量上添加SELECT COUNT来获取totalRows并将其放入输出参数中.

我做了一些测试并且使用了相当简单的查询版本(没有sortBy和没有过滤器),方法1似乎排在最前面(几乎是其他2的两倍).然后我决定测试可能我需要复杂性,我需要sql存储过程.有了这个,我得到方法1的时间几乎是其他两种方法的两倍.这看起来很奇怪.

有没有什么好理由我不应该摒弃CTE并坚持使用方法3？

更新 – 2012年3月15日

我尝试调整方法1将页面从CTE转储到临时表中,以便我可以提取TotalRows,然后只选择结果集的相关列.这似乎显着增加了时间(超出我的预期).我应该补充一点,我在使用sql Server Express 2008的笔记本电脑上运行它(我只提供所有这些)但仍然比较应该是有效的.

我再次看了动态sql 方法.事实证明我并没有真正做到这一点(只是将字符串连接在一起).我在sp_executesql的文档中设置了它(带有参数描述字符串和参数列表),并且它更具可读性.此方法在我的环境中运行速度最快.为什么那仍然让我感到困惑,但我想在Hogan的评论中暗示了答案.

解决方法

我很可能将@SortBy参数拆分为两个,@ SortColumn和@SortDirection,并像这样使用它们：

…
ROW_NUMBER() OVER (
  ORDER BY CASE @SortColumn
    WHEN 'Name'      THEN Name
    WHEN 'OtherName' THEN OtherName
    …
  END *
  CASE @SortDirection
    WHEN 'DESC' THEN -1
    ELSE 1
  END
) AS Row
…

这就是如何定义TotalRows列(在主选择中)：

…
COUNT(*) OVER () AS TotalRows
…

sql-server – 在存储过程中分页,排序和过滤(SQL Server)

解决方法

猜你在找的MsSQL相关文章