mssql 不用存儲過程，在查詢語句后獲取指定頁數(shù)記錄集同時(shí)獲得總查詢數(shù)？

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

如果支持窗口函數(shù)的話，用窗口函數(shù)，否則用子查詢方式

-- 窗口函數(shù)

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

改造前：

        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 的啟發(fā)）后：

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))
        };