mssql 不用存储过程，在查询语句后获取指定页数记录集同时获得总查询数？

Question

SELECT *
FROM
    (
        SELECT  ROW_NUMBER() OVER (ORDER BY ID) AS RowNumber, NAME FROM TB_USERS WHERE LEVEL = 2
    )   as A
WHERE rownumber between 20 and 30

这样得到的是 查询后的指定页数记录集（20条-30条），但是如何同时得到总查询数 ？

Answer 1

SELECT *
FROM

(
    SELECT  ROW_NUMBER() OVER (ORDER BY ID) AS RowNumber, NAME,count(*) over as Total FROM TB_USERS WHERE LEVEL = 2
)   as A

WHERE rownumber between 20 and 30

Answer 2

如果支持窗口函数的话，用窗口函数，否则用子查询方式

-- 窗口函数

SELECT *,count(*) as Total
FROM
    (
        SELECT  ROW_NUMBER() OVER (ORDER BY ID) AS RowNumber, NAME FROM TB_USERS WHERE LEVEL = 2
    )   as A
WHERE rownumber between 20 and 30

-- 子查询
SELECT *
FROM
    (
        SELECT  ROW_NUMBER() OVER (ORDER BY ID) AS RowNumber, NAME,
        (SELECT COUNT(*) FROM TB_USERS WHERE LEVEL = 2) AS Total
         FROM TB_USERS WHERE LEVEL = 2
    )   as A
WHERE rownumber between 20 and 30

Answer 3

改造前：

        string strSql = @"DECLARE @TempTable Table(num int, id int, title nvarchar(200), beizhu1 nvarchar(500), c_picurl1 nvarchar(300), g_id nvarchar(100), theMonth varchar(10), theDay varchar(10)) 
            insert into @TempTable
            select row_number() over(order by n_is_head, n_order desc, d_list_date desc, id desc) AS num, id, c_info_title, beizhu1, c_picurl1, g_id, Month(d_list_date) theMonth, Day(d_list_date) theDay
            from B_NEWS
            where n_is_active=1 and c_kind_num='30'
            select top(@num) *, (select count(*) from @TempTable) AS total
            from @TempTable
            where num>@index";

        SqlParameter[] paras ={
            new SqlParameter("@num", perPageCount),
            new SqlParameter("@kind", curKind),
            new SqlParameter("@index", perPageCount*(curPage-1))
        };

        return DbHelperSQL.Query(strSql, paras).Tables[0];

改造（受 ylka 的启发）后：

string strSql = @"select top(@num) * 
from ( select count(*) over() AS Total, row_number() over(order by n_is_head, n_order desc, d_list_date desc, id desc) AS num, id, c_info_title, beizhu1, c_picurl1, g_id, Year(d_list_date) theYear, Month(d_list_date) theMonth, Day(d_list_date) theDay
from B_NEWS
where n_is_active=1 and c_kind_num=@kind) AS T
where num>@index";

        SqlParameter[] paras ={
            new SqlParameter("@num", perPageCount),
            new SqlParameter("@kind", curKind),
            new SqlParameter("@index", perPageCount*(curPage-1))
        };