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

Question

在 SQL Server 中查询表时，我尝试仅获取当前页的记录。但是，我需要为特定查询返回的记录总数来计算页数。如何在不编写另一个查询来计算记录的情况下有效地执行此操作。

 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，则可以通过将两个相加来计算记录总数行计数在一起并减一。结果集中的任何行都将具有正确的总行数:

 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