我试图在一个存储过程中返回一页数据以及所有数据的行计数,如下所示：
WITH Props AS
(
SELECT *,ROW_NUMBER() OVER (ORDER BY PropertyID) AS RowNumber
FROM Property
WHERE PropertyType = @PropertyType AND ...
)
SELECT * FROM Props
WHERE RowNumber BETWEEN ((@PageNumber - 1) * @PageSize) + 1 AND (@PageNumber * @PageSize);
我无法返回行数(最高行号).
我知道这已经被讨论了(我看过这个：
Efficient way of getting @@rowcount from a query using row_number),但是当我在CTE中添加COUNT(x)OVER(PARTITION BY 1)时,性能下降,上面的查询通常不花时间永久执行.我认为这是因为计数是为每一行计算的？我似乎无法在另一个查询中重用CTE. Table Props拥有10万条记录,CTE返回5k条记录.
解决方法
在T-sql中应该是
;WITH Props AS
(
SELECT *,ROW_NUMBER() OVER (ORDER BY PropertyID) AS RowNumber
FROM Property
WHERE PropertyType = @PropertyType AND ...
),Props2 AS
(
SELECT COUNT(*) CNT FROM Props
)
-- Now you can use even Props2.CNT
SELECT * FROM Props,Props2
WHERE RowNumber BETWEEN ((@PageNumber - 1) * @PageSize) + 1 AND (@PageNumber * @PageSize);
现在你有CNT在每一行…或者你想要一些不同的东西？你想要一个只有计数的第二个结果集？然后做吧！
-- This could be the second result-set of your query. SELECT COUNT(*) CNT FROM Property WHERE PropertyType = @PropertyType AND ...
