两个高效的ASP分页函数（统计记录数，分页提取记录）
<%
' /*智能返回分页SQL语句*/
' /// <summary>
' /// 功能:智能返回分页SQL语句
' /// </summary>
' /// <param name="primaryKey">主键（不能为空）</param>
' /// <param name="queryFields">提取字段（不能为空）</param>
' /// <param name="tableName">表（理论上允许多表）</param>
' /// <param name="condition">条件（可以空）</param>
' /// <param name="OrderBy">排序，格式：字段名+""+ASC（可以空）</param>
' /// <param name="pageSize">分页数（不能为空）</param>
' /// <param name="pageIndex">当前页，起始为：1（不能为空）</param>
' /// <returns></returns>
Public Function GetPageListSql( primaryKey, queryFields, tableName, condition, orderBy, pageSize, pageIndex)
Dim strTmp,SqlSelect,SqlPrimaryKeySelect,strOrderBy,strWhere,strTop,pageindexsize
strTmp=""
'//---strTmp用于返回的SQL语句
SqlSelect = ""
SqlPrimaryKeySelect = ""
strOrderBy = ""
strWhere = " where 1=1 "
strTop = ""
pageindexsize = 0
' //0：分页数量
' //1:提取字段
' //2:表
' //3:条件
' //4:主键不存在的记录
' //5:排序
SqlSelect = " select top {0} {1} from {2} {3} {4} {5}"
' //0:主键
' //1:TOP数量,为分页数*(排序号-1)
' //2:表
' //3:条件
' //4:排序
SqlPrimaryKeySelect = " and {0} not in (select {1} {0} from {2} {3} {4}) "
if orderBy <> "" then
strOrderBy = " order by "&orderBy
End if
if condition <> "" then
strWhere =strWhere&" and "&condition
pageindexsize = (pageIndex - 1) * pageSize
End if
if cint(pageindexsize) > 0 then
strTop = " top " & pageindexsize
SqlPrimaryKeySelect = Replace(Replace(Replace(Replace(Replace(SqlPrimaryKeySelect,"{0}", primaryKey),"{1}", strTop),"{2}", tableName),"{3}", strWhere),"{4}", strOrderBy)
strTmp = Replace(Replace(Replace(Replace(Replace(Replace(SqlSelect,"{0}", pageSize),"{1}", queryFields),"{2}", tableName),"{3}", strWhere),"{4}", SqlPrimaryKeySelect),"{5}", strOrderBy)
else
strTmp = Replace(Replace(Replace(Replace(Replace(Replace(SqlSelect,"{0}", pageSize),"{1}", queryFields),"{2}", tableName),"{3}", strWhere),"{4}", ""),"{5}", strOrderBy)
End if
GetPageListSql= strTmp
End Function
' /*分页查询数据记录总数获取*/
' /// <summary>
' ///功能: 分页查询数据记录总数获取
' /// </summary>
' /// <param name="ptbName">----要显示的表或多个表的连接</param>
' /// <param name="pID">----主表的主键</param>
' /// <param name="pstrCondition">----查询条件,不需where</param>
' /// <param name="pDist">----是否添加查询字段的 DISTINCT 默认0不添加/1添加</param>
' /// <returns></returns>
Public Function GetPageListCounts( pID, ptbName, pstrCondition, pDist)
' //---存放取得查询结果总数的查询语句
' //---对含有DISTINCT的查询进行SQL构造
' //---对含有DISTINCT的总数查询进行SQL构造
Dim strTmp,SqlSelect,SqlCounts
strTmp = ""
SqlSelect = ""
SqlCounts = ""
if CInt(pDist) = 0 then
SqlSelect = "Select "
SqlCounts = "COUNT(*)"
else
SqlSelect = "Select DISTINCT "
SqlCounts = "COUNT(DISTINCT " & pID& ")"
End if
if pstrCondition ="" then
strTmp = SqlSelect & " "& SqlCounts & " FROM " & ptbName & ""
else
strTmp = SqlSelect & " " & SqlCounts & " FROM "& ptbName& " Where (1=1) and " & pstrCondition
End If
GetPageListCounts= strTmp
End Function
%>