ASP构造大数据量的分页SQL语句。

领测软件测试网 看了铁拳的存储过程“实现千万级数据的分页显示！”，觉得不错，我给改成了ASP代码了。

<%@LANGUAGE="VBSCRIPT" CODEPAGE="936"%>
<%
@#分页SQL语句生成代码
Function GetPageSQL(tblName,fldName,PageSize,PageIndex,OrderType,strWhere)
Dim strTemp,strSQL,strOrder

@#根据排序方式生成相关代码
if OrderType=0 then
strTemp=">(select max([" & fldName & "])"
strOrder=" order by [" & fldName & "] asc"
else
strTemp="<(select min([" & fldName & "])"
strOrder=" order by [" & fldName & "] desc"
end if

@#若是第1页则无须复杂的语句
if PageIndex=1 then
strTemp=""
if strWhere<>"" then
strTmp = " where " + strWhere
end if
strSQL = "select top " & PageSize & " * from [" & tblName & "]" & strTmp & strOrder
else @#若不是第1页，构造SQL语句
strSQL="select top " & PageSize & " * from [" & tblName & "] where [" & fldName & "]" & strTemp & _
" from (select top " & (PageIndex-1)*PageSize & " [" & fldName & "] from [" & tblName & "]"
if strWhere<>"" then
strSQL=strSQL & " where " & strWhere
end if
strSQL=strSQL & strOrder & ") as tblTemp)"
if strWhere<>"" then
strSQL=strSQL & " And " & strWhere
end if
strSQL=strSQL & strOrder
end if
GetPageSQL=strSQL @#返回SQL语句
End Function
response.write(GetPageSQL("message","messageid",10,1,0,""))
%>

文章来源于领测软件测试网 https://www.ltesting.net/