在 SQL Server 2012 中获取总计数和分页的更好方法

2022-01-03 00:00:00 sql-server pagination sql-server-2012

我需要获取记录总数和分页.目前我正在按照下面列出的 SQL Server 2012 进行操作.这需要一个单独的查询来获取计数.SQL Server 2012 有什么改进的方法吗?

I have requirement to get the total count of records along with paging. At present I am doing it as listed below in SQL Server 2012. This needs a separate query for getting count. Is there any improved way in SQL Server 2012?

ALTER PROCEDURE dbo.tpGetPageRecords
(
    @OffSetRowNo INT,     
    @FetchRowNo INT,
    @TotalCount INT OUT
) 
AS 

SELECT CSTNO, CSTABBR 
FROM DBATABC
WHERE CSTABBR LIKE 'A%'
ORDER BY CSTNO
OFFSET ( @OffSetRowNo-1 ) * @FetchRowNo ROWS
FETCH NEXT @FetchRowNo ROWS ONLY

SET @TotalCount = 
(SELECT COUNT(*)
FROM DBATABC
WHERE CSTABBR LIKE 'A%')


GO

推荐答案

如果我们被允许更改合同，您可以:

If we're allowed to change the contract, you can have:

SELECT CSTNO, CSTABBR,COUNT(*) OVER () as TotalCount
FROM DBATABC
WHERE CSTABBR LIKE 'A%'
ORDER BY CSTNO
OFFSET ( @OffSetRowNo-1 ) * @FetchRowNo ROWS
FETCH NEXT @FetchRowNo ROWS ONLY

现在总数将作为结果集中的单独列提供.不幸的是，无法在同一语句中将此值分配给变量，因此我们不能再将其作为 OUT 参数提供.

And now the total will be available as a separate column in the result set. Unfortunately, there's no way to assign this value to a variable in this same statement, so we can no longer provide it as an OUT parameter.

这使用了 OVER 子句(自 2005 年可用)允许在整个(无限)结果集上计算聚合，而无需 GROUPing.

This uses the OVER clause (available since 2005) to allow an aggregate to be computed over the entire (unlimited) result set and without requiring GROUPing.

相关文章