實(shí)現(xiàn)上千萬條數(shù)據(jù)的分頁顯示！
2024-07-21 02:07:47
供稿：網(wǎng)友
-- 獲取指定頁的數(shù)據(jù)create procedure getrecordfrompage @tblname varchar(255), -- 表名 @fldname varchar(255), -- 字段名 @pagesize int = 10, -- 頁尺寸 @pageindex int = 1, -- 頁碼 @iscount bit = 0, -- 返回記錄總數(shù), 非 0 值則返回 @ordertype bit = 0, -- 設(shè)置排序類型, 非 0 值則降序 @strwhere varchar(1000) = '' -- 查詢條件 (注意: 不要加 where)as
declare @strsql varchar(6000) -- 主語句declare @strtmp varchar(100) -- 臨時(shí)變量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
