<%
'********************************
'陈惠添编写
'分页SQL语句生成函数
'表名：tblName
'字段名：fldName
'每页显示条数：PageSize
'第几页：PageIndex
'ordertype:0升序，1为降序
'查询条件:strWhere--->可自已定义
'调用方法：call GetPageSQL(tblName,fldName,PageSize,PageIndex,OrderType,strWhere)
'目的：提高分页的速度
'*******************************
Function GetPageSQL(tblName,fldName,maxminname,PageSize,PageIndex,OrderType,strWhere)
Dim strTemp,strSQL,strOrder

    '根据排序方式生成相关代码
    if OrderType=0 then
        strTemp=">(select max([" & maxminname & "])"
        strOrder=" order by " & fldName & " asc"
    else
        strTemp="<(select min([" & maxminname & "])"
        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 [" & maxminname & "]" & strTemp & _
        " from (select top " & (PageIndex-1)*PageSize & " [" & maxminname & "] 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
%>
