ASP构造大数据量的分页SQL语句 z -- 77家的会客厅 3rgb.com

ASP构造大数据量的分页SQL语句 z: Weather:4/11度.好冷啊.; 2004-10-26; <%
'分页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,""))
%>
来源:http://blog.csdn.net/ppluncle/archive/2004/07/09/37999.aspx; Views(4094) | Comments(0) | In: web develop |