SQL语句 分页查询数据 - 透明的鱼！

SQL语句 分页查询数据

分页查询详情：

分页查询SQL语句

select top (@limit) * from (select row_number() over(order by FId desc) as rownumber,* from T_Products ) temp_row where rownumber>@offset 

@limit：条数

@offset ：最小条数

order by FId desc：数据根据FId倒序

T_Products：表格名称

分页查询通常配合计算sql计算总条数语句（select COUNT(1) from T_Products）使用

 

实例：

 /// <summary>
        /// 前台分页查询产品数据
        /// </summary>
        /// <param name="limit">条数</param>
        /// <param name="offset">最小条数</param>
        /// <param name="total">返回总条数</param>
        /// <returns></returns>
        public DataTable GetQueryList(int limit, int offset, string Type, out int total){
       //使用sql语句分页查询数据
                string sql = "select top (@limit) * from (select row_number() over(order by FId desc) as rownumber,* from T_Products ) temp_row where rownumber>@offset ";
                //计算数据总条数
                string totalsql = "select COUNT(1) from T_Products ";
                total = Convert.ToInt32(SqlHelper.ExecuteScalar(SqlHelper.connStr, totalsql));
                //使用键值对 对sql语句里的占位建进行替换
                SqlParameter[] para = new SqlParameter[]
                {
                new SqlParameter("@limit",SqlDbType.Int),
                new SqlParameter("@offset",SqlDbType.Int),
                
                };
                para[0].Value = limit;
                para[1].Value = offset;
                //返回一个Table
                return SqlHelper.ExecuteDataTable(SqlHelper.connStr, sql, para);
}