1.两种方式
1.1:row_number() over()函数
1.2:OFFSET ….FETCH
OFFSET { integer_constant | offset_row_count_expression } { ROW | ROWS }
指定在从查询表达式中开始返回行之前,将跳过的行数。OFFSET 子句的参数可以是大于或等于零的整数或表达式。ROW 和 ROWS 可以互换使用。
FETCH { FIRST|NEXT } <行计数表达式> { ROW|ROWS } ONLY
指定在处理 OFFSET 子句后,将返回的行数。FETCH 子句的参数可以是大于或等于 1 的整数或表达式。ROW 和 ROWS 可以互换使用。同样,FIRST 和 NEXT 可以互换使用。
每页10条,查询第10页的数据
select * from (select row_number() over(order by U_sortNo) as Rm,* from A01 where u_areacode=’130000′) as t where 100<Rm and Rm<=110
SELECT A0100,COUNT(1) OVER() AS total
FROM A01
where u_areacode=’130000′
ORDER BY U_sortNo
OFFSET 100 ROWS FETCH NEXT 10 ROWS ONLY
说明跳过100行不包括第100条数据,并查出来之后的10的数据,也就是101-110中的十条数据
/// 分页 /// </summary> /// <param name="pageIndex">获取当前页码默认为1</param> /// <param name="pageSize">获取每页显示多少条记录</param> public string GetUserList(int pageIndex, int pageSize,string orderField, string orderType) { int count; string sql = @"select a.*,b.Title,b.Content from Power_user a left join Knowledge b on a.ID = b.CreateUserID"; string strsql = string.Format("select * from ({0}) T_TT where 1=1 ", sql); StringBuilder stringBuilder = new StringBuilder(); int num = (pageIndex-1) * pageSize; int num2 = pageIndex * pageSize; string str; if (!string.IsNullOrEmpty(orderField)) { str = "Order By " + orderField + " " + orderType; } else { str = "order by (select 0)"; } stringBuilder.Append("Select * From (Select ROW_NUMBER() Over (" + str + ")"); stringBuilder.Append(string.Concat(new object[] { " As rowNum, * From (", strsql, ") As T ) As N Where rowNum > ", num, " And rowNum <= ", num2 })); count = System.Convert.ToInt32(SqlHelper.ExecuteScalar(SqlHelper.ConnectionString, CommandType.Text, "Select Count(1) From (" + strsql + ") As t", null)); ; DataTable dt = SqlHelper.ExecuteDataTable(SqlHelper.ConnectionString, CommandType.Text, stringBuilder.ToString(), null); string Json = JsonConvert.SerializeObject(dt); List<Power_User> userModel = SqlHelper.ExecuteClass<Power_User>(SqlHelper.ConnectionString, strsql, null); //string b = JsonConvert.SerializeObject(userModel); return Json; }