使用 row_number 从查询中获取 @@rowcount 的有效方法 [英] Efficient way of getting @@rowcount from a query using row_number

问题描述

我有一个使用 SQL Server 2005 中的 row_number over() 功能的昂贵查询.当查询被分页时，我只返回这些记录的子列表.但是，我还想返回记录总数，而不仅仅是分页子集.有效地运行查询两次以获取计数是不可能的.

I have an expensive query using the row_number over() functionality in SQL Server 2005. I return only a sub list of those records as the query is paginated. However, I would like to also return the total number of records, not just the paginated subset. Running the query effectively twice to get the count is out of the question.

选择 count(*) 也是不可能的，因为当我尝试过这个时，性能绝对糟糕.

Selecting count(*) is also out of the question as the performance is absolutely terrible when I've tried this.

我真正喜欢的是@@ROW_NUMBERROWCOUNT :-)

What I'd really love is @@ROW_NUMBERROWCOUNT :-)

推荐答案

检查 COUNT(*) 与 OVER(PARTITON BY..) 一起使用时的聚合，如下所示:

Check out the COUNT(*) aggregate when used with OVER(PARTITON BY..), like so:

    SELECT
     ROW_NUMBER() OVER(ORDER BY object_id, column_id) as RowNum
    , COUNT(*) OVER(PARTITION BY 1) as TotalRows
    , * 
    FROM master.sys.columns

恕我直言，这是最好的方法，无需进行两次查询.

This is IMHO the best way to do it without having to do two queries.

这篇关于使用 row_number 从查询中获取 @@rowcount 的有效方法的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！