关于数据库分页总结
1.中小型数据库可使用数据库分页
Grid1.RecordCount = GetTotalCount();
table = GetPagedDataTable(Grid1.PageIndex, Grid1.PageSize);
(1)指定数据的总数
Grid1.RecordCount = GetTotalCount();
private int GetTotalCount()
{
return DataSourceUtil.GetDataTable2().Rows.Count;
}
(2)获取分页数据
//参数 第N页,每页总数 private DataTable GetPagedDataTable(int pageIndex, int pageSize) { DataTable source = DataSourceUtil.GetDataTable2(); DataTable paged = source.Clone(); int rowbegin = pageIndex * pageSize; int rowend = (pageIndex + 1) * pageSize; if (rowend > source.Rows.Count) { rowend = source.Rows.Count; } for (int i = rowbegin; i < rowend; i++) { paged.ImportRow(source.Rows[i]); } return paged; }
2.大型数据表的分页(因1会读取表内所有的记录,故用SQL语句改进)
思路:
(1)子查询获取第N-1页的数据
(2)外查询使用NOT IN 从子查询中获取数据
string sql = "SELECT TOP " + pageSize + " * from tb_queue WHERE qu_id NOT IN " + "(SELECT TOP " + pageIndex * pageSize + " qu_id from tb_queue order by qu_id desc) " + " order by qu_id desc";
3.对于2的再次改进
string sql2; if (pageIndex * pageSize > 0) { sql2 = "SELECT TOP " + pageSize + " * from tb_queue WHERE qu_id < " + " (select MIN(qu_id) from (SELECT TOP " + pageIndex * pageSize + " qu_id from tb_queue order by qu_id desc) as qu_newid) " + " order by qu_id desc"; } else { sql2 = "SELECT TOP " + pageSize + " * from tb_queue order by qu_id desc "; }
4.SQL2005及更高版本,可以使用 row_number来排序
思路:给每条记录编好顺序号,然后根据顺序号来得到 (n-1)* pagesize +1 到 n* pagesize之间的数据
select * from
( select *,RN=row_number() over (order by rid asc) from tb_record ) as t
where t.RN between ((n-1)* pagesize +1) and n* pagesize
5.存储过程
待完结…………