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