sql server中的分頁資料查詢

標籤：

1.引言

    今天在工作中遇到一個需要進行sql server分頁資料查詢的問題，但是分頁資料查詢的sql卻忘記了，最終通過查詢資料解決了該問題。現在把解決方案記下，以備查閱。

在這裡需要感謝部落格園的Qlin

 

2.資料分頁語句

假設需要查詢表為Test，Test表中有個欄位為ID（我這裡用的是int型），當前頁pageIndex=5，頁大小pageSize=10。則分頁查詢語句如下：

SELECT * FROM (  SELECT ROW_NUMBER() OVER (ORDER BY ID) AS ROWID,* FROM Test) AS T WHERE T.ROWID BETWEEN (pageIndex-1) * pageSize+1 and pageIndex * pageSize;

3. C#實現

    　　 /// <summary>        /// 擷取分頁資料列表        /// </summary>        /// <param name="strFieldList">查詢欄位</param>        /// <param name="strWhere">查詢條件</param>        /// <param name="strOrderBy">排序欄位</param>        /// <param name="pageIndex">當前頁</param>        /// <param name="pageSize">頁大小</param>        /// <returns></returns>        public DataSet GetListByPage(string strFieldList, string strWhere, string strOrderBy, int pageIndex, int pageSize)        {            StringBuilder strSql = new StringBuilder();            strSql.Append("select");            if (string.IsNullOrEmpty(strFieldList))  //去除null            {                strFieldList = strFieldList.Trim();                if (string.IsNullOrEmpty(strFieldList)) // 去除只有空格的字串                {                    strFieldList = " * ";                }            }            strSql.Append(" " + strFieldList + " ");            strSql.Append(" from (select row_number() over (order by id) as rowid,");            strSql.Append(strFieldList + "from Test");            if (!string.IsNullOrEmpty(strWhere)) //去除null            {                strWhere = strWhere.Trim().ToLower();                if (!string.IsNullOrEmpty(strWhere)) // 去除只有空格的字串                {                    strWhere = (strWhere.StartsWith("where")) ? " " + strWhere : " where " + strWhere;                    strSql.Append(strWhere);                }            }            strSql.Append(") as t ");            if (!string.IsNullOrEmpty(strWhere)) // 沒有查詢條件            {                strWhere += " and t.rowId between {0} and {1}";            }            else            {                strWhere = "where t.rowId between {0} and {1}";            }            strWhere = string.Format(strWhere, (pageIndex - 1) * pageSize + 1, pageIndex * pageSize);            strSql.Append(strWhere);            if (!string.IsNullOrEmpty(strOrderBy)) //去除null            {                strOrderBy = strOrderBy.Trim().ToLower();                if (!string.IsNullOrEmpty(strOrderBy)) // 去除只有空格的字串                {                    strOrderBy = (strOrderBy.StartsWith("order by")) ? " " + strOrderBy : " order by " + strOrderBy;                    strSql.Append(strOrderBy);                }            }            return DbHelperSQL.Query(strSql.ToString());        }

4. 原文

Sql Server 資料分頁

sql server中的分頁資料查詢