sqlPS= "select * From Employee Left JOIN Department ON Employee.DepartmentID=Department.DepartmentID Order By Employee.DepartmentID,Employee.EmNO "
call GetPage_RSDT(sqlPS,con,intListCount, "PageControl ", "EmID ", "frmList ",rsPS)
'|ResultPageRS(Sql串,连接,每页行数,页号,过滤字段,表单名,)
function GetPage_RSDT(strSqlText,objConn,intPageSize,PageControl,KeyID, strFormName, Ref_RS)
set Rsdt=Server.CreateObject( "ADODB.Recordset ")
if IsNumeric(PageControl) then
intPageIndex = PageControl
CurrentPage = "PageControl "
else
CurrentPage = PageControl
intPageIndex = Trim(Request(PageControl))
if IsNumeric(intPageIndex) then
if intPageIndex <= 0 then intPageIndex = 1
else
intPageIndex = 1
end if
end ifdim strSql,strPageOut,intAll,intNot,intPos,intOrder,intWhere
RsTotalCount = dbCount(strSqlText,objConn)
strSql = Trim(strSqlText)
intPos_Select = instr(1,strSql, "select ",1) + 6
'response.Write(Left(strSql,6)) & "| <BR> "
'response.Write(Left(strSql,7)) & "| <BR> "
'response.Write( "| "& mid(strSql,7)) & "| <BR> "
'response.Write( "| "& mid(strSql,8)) & "| <BR> "
'response.End
strSql_1 = Left(strSql,intPos_Select) '|取出 select
strSql_2 = Mid(strSql,intPos_Select+1) '|取出 select 之后的内容
intPos = InStr(UCase(strSql_2), " FROM ") '|取出表单
intWhere = InStr(UCase(strSql_2), " WHERE ") '|取出条件
intOrder = InStr(UCase(strSql_2), " ORDER ") '|取出排序 Sql_7strSql_6 = left(strSql_2,intOrder)
if intOrder > 0 then
strSql_7 = mid(strSql_2,intOrder)
strSql_2 = strSql_6
end if
'|Response.Write(strSql_7 & " <hr> ")
if intWhere > 0 then
strSql_2 = left(strSql_2,intWhere+6) & "( " & mid(strSql_2,intWhere+7) & ") " '|重载变量
end ifintAll = cint(intPageIndex) * cint(intPageSize)
intNot = cint(intPageIndex - 1) * cint(intPageSize)strSql_3 = " Top " & cint(intPageSize) & " "
strSql_4 = " Top " & intNot & " "if intWhere > 0 then
strSql_5 = " AND (( " & KeyID & " NOT IN ( " & strSql_1 & strSql_4 & " " & KeyID & " " & mid(strSql_2,intPos) & " " & strSql_7 & "))) "
else
strSql_5 = " WHERE (( " & KeyID & " NOT IN ( " & strSql_1 & strSql_4 & " " & KeyID & " " & mid(strSql_2,intPos) & " " & strSql_7 & "))) "
end if'response.Write(strSql_1 & " <hr> ") '|select
'response.Write(strSql_3 & " <hr> ") '|Top 4
'response.Write(strSql_2 & " <hr> ") '|源SQL * - Where ..
'response.Write(strSql_5 & " <hr> ") '|附加 where
'response.Write(strSql_7 & " <hr> ") '|Order bystrSql = strSql_1 & strSql_3 & strSql_2 & strSql_5 & strSql_7
'| Response.Write(strSql) '| "SELECT TOP 9 * FROM Manager WHERE (ID NOT IN (SELECT TOP 2 ID FROM Manager)) "
'response.Write strSql
'||response.EndRsdt.Open strSql,con,1,1
if TypeName(Ref_RS) = "Recordset " then set Ref_RS = Rsdtcall getPageControl(intPageIndex,intPageSize,RsTotalCount,strFormName,CurrentPage)
'Rsdt.Close()
'set Rsdt = nothing
End function
'方法二
记录集(startPage+pageSize)-记录集(startPage)
我没有用IN,是觉得这样性能可能高些.
declare @pagesize int
declare @startpage int
declare @sql varchar(4000)
set @startpage=30
set @pagesize=10set @sql= 'select ee.* from (select top '+ convert(varchar,@pagesize+@startpage) + ' aa.* from t4 aa) ee where
not exists(select ff.* from (select top '+convert(varchar,@startpage) + ' bb.SID from t4 bb) ff where ee.SID=ff.SID) 'exec(@sql)
'方法三
高效的分页存储过程
摘自CSDN
CREATE PROCEDURE PAGINATION
@tblName varchar(255),
@strGetFields varchar(1000) = '* ',
@fldName varchar(255)= ' ',
@PageSize int = 10,
@PageIndex int = 1,
@doCount bit = 0,
@OrderType bit = 0,
@strWhere varchar(1500) = ' '
AS
declare @strSQL varchar(5000)
declare @strTmp varchar(110)
declare @strOrder varchar(400)
if @doCount != 0
begin
if @strWhere != ' '
set @strSQL = 'select count(*) as Total from [ ' + @tblName + '] where '+@strWhere
else
set @strSQL = 'select count(*) as Total from [ ' + @tblName + '] '
end
else
begin
if @OrderType != 0
begin
set @strTmp = ' <(select min '
set @strOrder = ' order by [ ' + @fldName + '] desc '
end
else
begin
set @strTmp = '> (select max '
set @strOrder = ' order by [ ' + @fldName + '] asc '
end
if @PageIndex = 1
begin
if @strWhere != ' '
set @strSQL = 'select top ' + str(@PageSize) + ' '+@strGetFields+ ' from [ ' + @tblName + '] where ' + @strWhere + ' ' + @strOrder
else
set @strSQL = 'select top ' + str(@PageSize) + ' '+@strGetFields+ ' from [ '+ @tblName + '] '+ @strOrder
end
else
begin
set @strSQL = 'select top ' + str(@PageSize) + ' '+@strGetFields+ ' 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) + ' '+@strGetFields+ ' from [ '
+ @tblName + '] where [ ' + @fldName + '] ' + @strTmp + '([ '
+ @fldName + ']) from (select top ' + str((@PageIndex-1)*@PageSize) + ' [ '
+ @fldName + '] from [ ' + @tblName + '] where ' + @strWhere + ' '
+ @strOrder + ') as tblTmp) and ' + @strWhere + ' ' + @strOrder
end
end
exec (@strSQL)
GO