分页查询的一个帮助类
分页sql查询在编程的应用很多,主要有存储过程分页和sql分页两种,我比较喜欢用sql分页,主要是很方便。为了提高查询效率,应在排序字段上加索引。sql分页查询的原理很简单,比如你要查100条数据中的30-40条,你先查询出前40条,再把这30条倒序,再查出这倒序后的前十条,最后把这十条倒序就是你想要的结果。 下面把sql分页查询的原理用sql语句表现一下:
-- 分页 升序(搜出的结果再倒序) SELECT TOP 10 * FROM (SELECT TOP 40 * from A01 ORDER BY A00 ASC) AS T ORDER BY A00 DESC
-- 分页 升序 SELECT * FROM (SELECT TOP 10 * FROM (SELECT TOP 40 * from A01 ORDER BY A00 ASC) AS T ORDER BY A00 DESC) AS T1 ORDER BY A00 ASC
-- 分页 降序(搜出的结果再倒序) SELECT TOP 10 * FROM (SELECT TOP 40 * from A01 ORDER BY A00 DESC) AS T ORDER BY A00 ASC
-- 分页 降序 SELECT * FROM (SELECT TOP 10 * FROM (SELECT TOP 40 * from A01 ORDER BY A00 DESC) AS T ORDER BY A00 ASC) AS T1 ORDER BY A00 DESC
为了应用的方便我把生成sql分页查询语句的写成了类SplitHelp
应用如下:
/**//// <summary> /// 分页查询例子 /// </summary> /// <param >当前页</param> /// <param >每页大小</param> /// <param >数据总条数</param> /// <param >数据库连接</param> /// <returns>查询IDbCommand</returns> public IDbCommand Search(int currentPage, int pagesize, out int count, IDbConnection cn) { //得到IDbCommand IDbCommand cmd = cn.CreateCommand(); cmd.CommandType = CommandType.Text; cmd.CommandText = "select count(tableTestID) from tableTest"; if (cn.State != ConnectionState.Open) cn.Open(); //得到数据总数 count = (int)cmd.ExecuteScalar();
//搜索的前n条 int topAll = SplitPage.GetTopNum(currentPage, pagesize, count);
//排序字段类 AscDescClass ascDesc = new AscDescClass("tableTestID", AscDescEnum.desc); //ascDesc.Add("tableTestID1", AscDescEnum.desc);
//此sql语名必须有排序,写出要查询前topAll条记录的sql string sql = String.Format(@"select top {0} * from tableTest order by {1}", topAll, ascDesc.GetAscDesString()); //最终sql cmd.CommandText = SplitPage.GetFinalSql(sql, ascDesc, pagesize, count, topAll, currentPage);
return cmd; } 当然,要想真正提高查询效率,最好还是用存储过程,这里写了两个例子,一个真对sql2000,一个真对sql2005
sql2000 create proc usp_UserGoldHistoryByDateRange ( @StartDate varchar(10), @EndDate varchar(10), @PageSize int, @PageIndex int, @RowCount int out ) as declare @StartRow int declare @EndRow int -- 计算当前页开始行 set @StartRow = (@PageIndex - 1) * @PageSize + 1 -- 计算当前页结束行 set @EndRow = @StartRow + @PageSize - 1
-- 建一张内存表用于存储检索结果 declare @temp table ( AutoID [int] IDENTITY (1, 1) NOT NULL, RowID [int] ) -- 执行检索 insert into @temp(RowID) select RowID from UserGoldHistory where left(DateTimeTag,10) between @StartDate and @EndDate
set @RowCount = @@ROWCOUNT
select * from UserGoldHistory where RowID in ( select RowID from @temp where AutoID between @StartRow and @EndRow)
sql2005 create proc proc_Split -- 设置每页的行数 @page_size int, -- 设置当前页 @page_current int, -- 总记录数 @rows_count int out as
select @rows_count=count(UserName) from ForumUser
DECLARE @start_row_num int DECLARE @end_row_num int -- 设置开始行号 SET @start_row_num = (@page_current - 1) * @page_size + 1 -- 设置结束行号 SET @end_row_num = @start_row_num + @page_size - 1;
WITH temptesttable AS ( SELECT ROW_NUMBER() OVER(ORDER BY UserName) AS row_number, * FROM ForumUser ) SELECT * from temptesttable WHERE row_number BETWEEN @start_row_num AND @end_row_num
posted on 2006-12-09 19:22 来问(zljGood@hotmail.com) 阅读(517) 评论(4) 编辑 收藏 引用 网摘 所属分类: SQL技术
评论 # re: 分页查询的一个帮助类 2006-12-10 11:03 THIN SQL语句何必要用临时表呢,要是要第100页呢,不是要先把几千条数据查出来? 子查询查出ID就行了吧 回复 更多评论
# re: 分页查询的一个帮助类 2006-12-10 13:19 S.Sams 数据一多,性能方面还是得考虑 回复 更多评论
# re: 分页查询的一个帮助类 2006-12-10 16:35 来问(zljGood@hotmail.com) @THIN
当然,要想真正提高查询效率,最好还是用存储过程,这里写了两个例子 多谢
sql2000 create proc usp_UserGoldHistoryByDateRange ( @StartDate varchar(10), @EndDate varchar(10), @PageSize int, @PageIndex int, @RowCount int out ) as declare @StartRow int declare @EndRow int -- 计算当前页开始行 set @StartRow = (@PageIndex - 1) * @PageSize + 1 -- 计算当前页结束行 set @EndRow = @StartRow + @PageSize - 1
-- 建一张内存表用于存储检索结果 declare @temp table ( AutoID [int] IDENTITY (1, 1) NOT NULL, RowID [int] ) -- 执行检索 insert into @temp(RowID) select RowID from UserGoldHistory where left(DateTimeTag,10) between @StartDate and @EndDate
set @RowCount = @@ROWCOUNT
select * from UserGoldHistory where RowID in ( select RowID from @temp where AutoID between @StartRow and @EndRow)
sql2005 create proc proc_Split -- 设置每页的行数 @page_size int, -- 设置当前页 @page_current int, -- 总记录数 @rows_count int out as
select @rows_count=count(UserName) from ForumUser
DECLARE @start_row_num int DECLARE @end_row_num int -- 设置开始行号 SET @start_row_num = (@page_current - 1) * @page_size + 1 -- 设置结束行号 SET @end_row_num = @start_row_num + @page_size - 1;
WITH temptesttable AS ( SELECT ROW_NUMBER() OVER(ORDER BY UserName) AS row_number, * FROM ForumUser ) SELECT * from temptesttable WHERE row_number BETWEEN @start_row_num AND @end_row_num