实现上千万条数据的分页显示！
2024-07-21 02:07:47
供稿：网友
-- 获取指定页的数据create procedure getrecordfrompage @tblname varchar(255), -- 表名 @fldname varchar(255), -- 字段名 @pagesize int = 10, -- 页尺寸 @pageindex int = 1, -- 页码 @iscount bit = 0, -- 返回记录总数, 非 0 值则返回 @ordertype bit = 0, -- 设置排序类型, 非 0 值则降序 @strwhere varchar(1000) = '' -- 查询条件 (注意: 不要加 where)as
declare @strsql varchar(6000) -- 主语句declare @strtmp varchar(100) -- 临时变量declare @strorder varchar(400) -- 排序类型
if @ordertype != 0begin set @strtmp = "<(select min" set @strorder = " order by [" + @fldname +"] desc"endelsebegin set @strtmp = ">(select max" set @strorder = " order by [" + @fldname +"] asc"end
set @strsql = "select top " + str(@pagesize) + " * from [" + @tblname + "] where [" + @fldname + "]" + @strtmp + "([" + @fldname + "]) from (select top " + str((@pageindex-1)*@pagesize) + " [" + @fldname + "] from [" + @tblname + "]" + @strorder + ") as tbltmp)" + @strorder
if @strwhere != '' set @strsql = "select top " + str(@pagesize) + " * from [" + @tblname + "] where [" + @fldname + "]" + @strtmp + "([" + @fldname + "]) from (select top " + str((@pageindex-1)*@pagesize) + " [" + @fldname + "] from [" + @tblname + "] where " + @strwhere + " " + @strorder + ") as tbltmp) and " + @strwhere + " " + @strorder
if @pageindex = 1begin set @strtmp = "" if @strwhere != '' set @strtmp = " where " + @strwhere
set @strsql = "select top " + str(@pagesize) + " * from [" + @tblname + "]" + @strtmp + " " + @strorderend
if @iscount != 0 set @strsql = "select count(*) as total from [" + @tblname + "]"
exec (@strsql)
go
