在SQL Server 2012中获取总计数和分页的更好方法 [英] Better way for Getting Total Count along with Paging in 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年以来可用) )，以允许在整个(无限)结果集中计算汇总，而无需GROUP ing.

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.

这篇关于在SQL Server 2012中获取总计数和分页的更好方法的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！