1.引言
今天在工作中遇到一个需要进行sql server分页数据查询的问题，但是分页数据查询的sql却忘记了，最终通过查询资料解决了该问题。现在把解决方法记下，以备查阅。
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 - ) * pageSize + , 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());
}