一个分页sql语句生成的function-优快云博客

本文介绍了一种基于VBScript的分页SQL语句生成方法，可根据不同排序方式、页面大小及位置生成相应的SQL查询语句，适用于需要进行大量数据分页展示的应用场景。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

        '==============================
        ''分页sql语句生成代码
        '==============================

        Function GetPageSql(TblName, FldName, PageSize, PageIndex, OrderType, StrWhere)
                Dim StrTemp, StrSql, StrOrder
                ''根据排序方式生成相关代码
                If OrderType = "Asc" 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
                                StrTemp = " Where " & StrWhere
                        End If
                        StrSql = "Select Top " & PageSize & " * From [" & TblName & "]" & StrTemp & 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
                ''返回sql语句
                GetPageSql = StrSql
        End Function

如何使用
        Dim StrWhere:StrWhere = ""    'where条件
        Set rs = server.CreateObject("adodb.recordset")
        sql = GetPageSql("表名","无重复值的排序字段名",每页记录数,当前页,"desc",StrWhere )
        rs.Open sql, conn, 1, 1