如何在 SELECT T-SQL 命令的结果集中包含返回的总行数? [英] How to include the total number of returned rows in the resultset from SELECT T-SQL command?

问题描述

我想问一下是否有办法将总行数作为附加列包含在 TSQL 查询返回的结果集中，同时使用 Row_Number 命令.>

例如，以类似于此的形式从对 Book 表的查询中获取结果集:

RowNum BookId BookTitle TotalRows-----------------------------------------1 1056 标题 1 52 1467 标题2 53 121 标题3 54 1789 标题4 55 789 标题5 5

查询是在存储过程中实现的自定义分页功能的一部分.目标是只返回当前页面Index的记录，并且受限于页面大小，还有select语句中的记录总数，以确定resultset页面的总数.

解决方案

在 SQL Server 2008 及更高版本中，添加 COUNT(*) OVER() 作为其中之一查询中的列名称，并将填充返回的总行数.

它在每一行中重复，但至少值是可用的.

许多其他解决方案不起作用的原因是，对于非常大的结果集，直到迭代所有行之后您才会知道总数，这在许多情况下是不切实际的(尤其是顺序处理解决方案).例如，此技术可在调用第一个 IDataReader.Read() 后为您提供总计数.

select COUNT(*) OVER() as Total_Rows, ... from ...

I would like to ask if there is a way to include the total number of rows, as an additional column, in the returned result sets from a TSQL query using also the Row_Number command.

For example, getting the results set from a query against Book table in a form similar to this:

RowNum   BookId     BookTitle    TotalRows
--------------------------------------------
1        1056       Title1       5    
2        1467       Title2       5    
3        121        Title3       5    
4        1789       Title4       5    
5        789        Title5       5

The query is part of custom paging functionality implemented in a stored procedure. The goal is to return back only the records for the current page Index and limited to the page size, but also the amount of total number of records in the select statement in order to determine the total number of resultset pages.

解决方案

In SQL Server 2008 and later, add COUNT(*) OVER () as one of the column names in your query and that will be populated with the total rows returned.

It is repeated in every single row but at least the value is available.

The reason why many other solutions do not work is that, for very large result sets, you will not know the total until after iterating all rows which is not practical in many cases (especially sequential processing solutions). This technique gives you the total count after calling the first IDataReader.Read(), for instance.

select COUNT(*) OVER () as Total_Rows, ... from ...

这篇关于如何在 SELECT T-SQL 命令的结果集中包含返回的总行数?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！