分页期间在SQL Server中获取记录总数的有效方法

Question

在sql
server中查询表时，im试图仅获取记录的当前页面。但是，我需要为特定查询返回的记录总数，以计算页面数。如何有效地做到这一点，而无需编写另一个查询来对记录进行计数。

 WITH allentities 
         AS (SELECT Row_number() OVER (ORDER BY se.entityid ASC) AS 
                    rowid 
                    ,empid
                    ,lastname 
                    ,firstname
                     ,d.depname

             FROM   emp e join dep d on e.depid=d.depid) 
    SELECT * 
    FROM   allentities 
    WHERE  rowid >= @pageid 
           AND rowid <= @pageid + 20

Answer 1

如果您添加第二种ROW_NUMBER()排序方式DESC而不是ASC，则可以通过将两个行计数加在一起再减去一个来计算记录总数。rsult集中的任何行都将具有正确的总行数：

 WITH allentities 
         AS (SELECT Row_number() OVER (ORDER BY se.entityid ASC) AS 
                    rowid 
                    ,empid
                    ,lastname 
                    ,firstname
                    ,d.depname 
                    ,ROW_NUMBER() OVER (ORDER BY se.entityid DESC) AS rowIdNeg
             FROM   emp e join dep d on e.depid=d.depid) 
    SELECT * 
            ,rowid + rowIdNeg - 1 as totalRecords
    FROM   allentities 
    WHERE  rowid >= @pageid 
           AND rowid <= @pageid + 20