mssql2000分页查询

分页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 name="currentPage">当前页</param>
/// <param name="pagesize">每页大小</param>
/// <param name="count">数据总条数</param>
/// <param name="cn">数据库连接</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

本文转自：http://hi.baidu.com/liulin0712/blog/item/ae664cc69e342b119c163d0b.html