How to include the total number of returned rows i

问题:

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 (SQL 2005) 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.

回答1:

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 ...

回答2:

In SQL Server 2005 and newer you can do this with a CTE:

WITH result AS (SELECT ... your query here ...)
SELECT
    *,
    (SELECT COUNT(*) FROM result) AS TotalRows
FROM result

In general I'd advise against doing this, but if you really need to then this is how to do it.

回答3:

Via comments attached to the question it's clear that this question relates to paging. In that scenario, there are two broad approaches:

1. Query all rows that match the search criteria (not just one page worth) and store into a table valued variable (along with a ROW_NUMBER). Then run two queries against that table valued variable: one to extract the desired page of data and the second to to get the total count.
2. Don't use a table-valued variable and just run the full query twice, once to get the page of data and once for the total count.

The first option works well if the total number of rows is measured in the thousands. If the total number is much higher, you best bet is to run the query twice.

This is a typical space/processing trade-off.

Your milage may vary - what works well for one situation may be terrible in another!

回答4:

Example using the AdventureWorks database

select 
    *, 
    TotalVolume = (select COUNT(*) from HumanResources.Department) 
from  HumanResources.Department

回答5:

Perhaps what you'er looking for is @@ROWCOUNT?

回答6:

SELECT  n ,
        COUNT(*) OVER ( PARTITION BY 1 )
FROM    ( SELECT    1 AS n
          UNION ALL
          SELECT    2 AS n
        ) AS t

Note that @@ROWCOUNT gives you row count from the previous command. Run this:

SELECT    1 AS n;

SELECT  n ,
        @@ROWCOUNT
FROM    ( SELECT    1 AS n
          UNION ALL
          SELECT    2 AS n
        ) AS t

回答7:

select *, @@rowcount from MyTable