數據庫分頁

sql如何返回第三或从第三条记录开始返回(分页问题)？ 作者：cg1 摘自：access911.net 编辑：cg1 更新日期：2003-2-9 浏览人次：1714 专题地址： http://www.access911.net/?kbid;72FAB41E14DC 简述： sql如何返回第三或从第三条记录开始返回(分页问题)？《查询》 阅读前需掌握： 难度等级： 15 问题： 如何用sql返回第三条或者第三条记录开始返回? 从数据库中的表中从第三条记录开始取，那么select该怎么写呢？ 回答： select * from table_name where id_field not in (select top 3 id_field from table_name) 注意，其实就是把 select top 语句写两遍，第二遍包含了第一遍，然后把是第一遍中的记录用not in剔除就可以了，所以两个where都必须包含相同的外加条件。 asp 中经常用来写 pageno 的程序，因为直接用ado的pagesize如果遇上几十万数据，用pagesize就完蛋喽。 关于 not in 语句速度比较慢，某人提示将not in改为 select table2.* from table2 left join table1 on table2.key=table1.key where (able1.key is null) 返回第3条记录用： select top 1 * from (SELECT top 3 a.编号 FROM a order by 编号) as b order by 编号 desc 如果用来进行分页，返回第100-150条记录，可以用 select top 50 * from (SELECT top 150 a.编号 FROM a order by 编号) as b order by 编号 desc 以下是 SQL SERVER 2000 T-SQL 分页代码 -------------------------- -- 分页代码 IF EXISTS (SELECT name FROM sysobjects WHERE name = N'p_GetTopic' AND type = 'P') DROP PROCEDURE p_GetTopic GO CREATE PROCEDURE p_GetTopic @PageSize int =15, @CurPage int =1, @OrderBy varchar(50) = 'desc' AS declare @sql varchar(4000) --declare @intCount int --select @intCount = count(*) from t_Issue if LOWER(@OrderBy) = 'asc' begin select @sql= 'select ti.*, tu.username, tu.usernickname, tu.ranknum, tu.credit, tr.roomname from (select top ' + cast(@PageSize as varchar(20)) + ' * from t_issue where topicid > isnull((select max(topicid) from (select top ' + cast((@PageSize * (@CurPage -1) ) as varchar(20)) + ' * from t_Issue order by topicid ) a),0) order by topicid) ti left join t_user tu on ti.postuserid = tu.userid left join t_room tr on ti.roomid = tr.roomid order by ti.topicid ' end else begin select @sql= 'select ti.*, tu.username, tu.usernickname, tu.ranknum, tu.credit, tr.roomname from (select top ' + cast(@PageSize as varchar(20)) + ' * from t_issue where topicid < isnull((select min(topicid) from (select top ' + cast((@PageSize * (@CurPage -1) ) as varchar(20)) + ' * from t_Issue order by topicid desc) a),99999999) order by topicid desc) ti left join t_user tu on ti.postuserid = tu.userid left join t_room tr on ti.roomid = tr.roomid order by ti.topicid desc ' end execute(@sql) select count(*) as RecordCount from t_issue GO -- ============================================= -- example to execute the store procedure -- ============================================= EXECUTE p_GetTopic 233,1 GO