SQL-Server分页查询多种方法讲解以及分页存储过程

首先说一下SQL server的分页与MySQL的分页的不同，mysql的分页直接是用limit (pageIndex-1),pageSize就可以完成，但是SQL server 并没有limit关键字，只有类似limit的top关键字。所以分页起来比较麻烦。

SQL server分页我所知道的就只有五种：三重循环；利用max（主键）; 利用not in 关键字；利用row_number关键字，offset/fetch next关键字

方法一：三重循环   先取前20页，然后倒序，取倒序后前10条记录，这样就能得到分页所需要的数据，不过顺序反了，之后可以将再倒序回来，也可以不再排序了，直接交给前端排序。

  还有一种方法也算是属于这种类型的，这里就不放代码出来了，只讲一下思路，就是先查询出前10条记录，然后用not in排除了这10条，再查询。

-- 设置执行时间开始，用来查看性能的
set statistics time on ;
-- 分页查询（通用型）
select * 
from (select top pageSize * 
from (select top (pageIndex*pageSize) * 
from student 
order by sNo asc ) -- 其中里面这层，必须指定按照升序排序，省略的话，查询出的结果是错误的。
as temp_sum_student 
order by sNo desc ) temp_order
order by sNo asc-- 分页查询第2页，每页有10条记录
select * 
from (select top 10 * 
from (select top 20 * 
from student 
order by sNo asc ) -- 其中里面这层，必须指定按照升序排序，省略的话，查询出的结果是错误的。
as temp_sum_student 
order by sNo desc ) temp_order
order by sNo asc
;

方法二：利用max（主键）

  先top前11条行记录，然后利用max（id）得到最大的id，之后再重新再这个表查询前10条，不过要加上条件，where id>max(id)。

set statistics time on;
-- 分页查询（通用型）
select top pageSize * 
from student 
where sNo>=
(select max(sNo) 
from (select top ((pageIndex-1)*pageSize+1) sNo
from student 
order by  sNo asc) temp_max_ids) 
order by sNo;-- 分页查询第2页，每页有10条记录
select top 10 * 
from student 
where sNo>=
(select max(sNo) 
from (select top 11 sNo
from student 
order by  sNo asc) temp_max_ids) 
order by sNo;

方法三：利用not in 关键字

  先用子查询查询出在目标范围之前的记录，再使用not in 取出不在子查询范围内的前N条记录

set statistics time on;
-- 分页查询（通用型）
select top pageSize * 
from student where id not in (select top  rownumber*pageSize id 
from student) set statistics time on;
-- 分页查询第2页，每页有10条记录
select top 10 * 
from student where id not in (select top  1*10 id 
from student) 

方法四：row_number() over(order by id)关键字

直接利用row_number() over(order by id)函数计算出行数，选定相应行数返回即可，不过该关键字只有在SQL server 2005版本以上才有。

set statistics time on;
-- 分页查询（通用型）
select top pageSize * 
from (select row_number() 
over(order by sno asc) as rownumber,* 
from student) temp_row
where rownumber>((pageIndex-1)*pageSize);set statistics time on;
-- 分页查询第2页，每页有10条记录
select top 10 * 
from (select row_number() 
over(order by sno asc) as rownumber,* 
from student) temp_row
where rownumber>10

第五种方法：offset /fetch next（2012版本及以上才有）

set statistics time on;
-- 分页查询（通用型）
select * from student
order by sno 
offset ((@pageIndex-1)*@pageSize) rows
fetch next @pageSize rows only;-- 分页查询第2页，每页有10条记录
select * from student
order by sno  
offset 10 rows
fetch next 10 rows only ;

offset A rows ,将前A条记录舍去，fetch next B rows only ，向后在读取B条数据。

分页的存储过程

create procedure paging_procedure
(	@pageIndex int, -- 第几页@pageSize int  -- 每页包含的记录数
)
as
begin select top (select @pageSize) *     -- 这里注意一下，不能直接把变量放在这里，要用selectfrom (select row_number() over(order by sno) as rownumber,* from student) temp_row where rownumber>(@pageIndex-1)*@pageSize;
end-- 到时候直接调用就可以了，执行如下的语句进行调用分页的存储过程
exec paging_procedure @pageIndex=2,@pageSize=10;

以下是我C#封装的一个分页查询方法 供大家参考

 public DataTable Select(int pageIndex,int pageSize){string sql = @"select * from Student order by Name offset @offset rowsfetch next @ofetch rows only ";DataSet ds=SqlHelper.GetDateSet(sql,new SqlParameter("offset",(pageIndex-1)* pageSize),new SqlParameter("ofetch", pageSize)); return ds.Tables[0];}public int GetCount(){string sql = @"select count(1) from Student";return (int)SqlHelper.GetObject(sql);}
调用DAL数据库方法ExcelDAL excelDAL=new ExcelDAL();public DataTable GetTable(int pageIndex,int pageSize,out int total){DataTable dt = excelDAL.Select(pageIndex,pageSize);total=excelDAL.GetCount();return dt;}
UI:   ExcelBLL excelBLL = new ExcelBLL();int pageIndex =1;//默认设置第一页int pageSixe = 5;//显示几条数据int total = 0;//总数据int maxPageIndex;//最大页码数private void  Page(){//绑定表格数据源 dataGridView1.DataSource = excelBLL.GetTable(pageIndex, pageSixe, out total);maxPageIndex = (int)Math.Ceiling(total / (double)pageSixe);label1.Text = pageIndex + "/" + maxPageIndex;this.textBox1.Text = "";}
上一页操作 button click
{if (pageIndex == 1) return;pageIndex--;Page();    
}
下一页操作 button click
{if (pageIndex == 1) return;pageIndex++;Page();    
}
根据下拉框的数据来分为显示几条数据 进行赋值  pageSixe=int.Parse(comboBox1.Text); 调用Page()刷新跳转某某页//跳转
private void button3_Click(object sender, EventArgs e)
{if (textBox1.Text != null){dataGridView1.DataSource = excelBLL.GetTable(int .Parse(this.textBox1.Text), pageSixe, out total);maxPageIndex = (int)Math.Ceiling(total / (double)pageSixe);if (int.Parse(this.textBox1.Text) > maxPageIndex){this.textBox1.Text = "";MessageBox.Show("没有当前页");return;}label1.Text = int.Parse(this.textBox1.Text) + "/" + maxPageIndex;}
}

总结  

 根据以上五种分页的方法执行的时间可以知道，以上五种分页方法中，第二，第三，第三,四,五种方法性能是差不多的，但是第一种性能很差，不推荐使用。还有就是这篇博客这是测试了小量数据，还没有分页大量数据，所以不清楚在大量数据要分页时哪种方法的性能更加好。我这里推荐第五种，毕竟第五种是SQL server公司升级后推出的新方法，所以应该理论上性能和可读性都会更加好