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

新客网 XKER.COM 2005-05-30 来源：收藏本文

<%@Language = "VBScript" Codepage = "936"%>
2

<%
3

'分页sql语句生成代码
4

Function GetPageSql(TblName,FldName,PageSize,PageIndex,OrderType,StrWhere)
5

Dim StrTemp,StrSql,StrOrder
6

'根据排序方式生成相关代码
7

If OrderType = 0 Then
8

StrTemp = "> (Select Max([" & FldName & "])"
9

StrOrder = " Order By [" & FldName & "] Asc"
10

Else
11

StrTemp = "< (Select Min([" & FldName & "])"
12

StrOrder = " Order By [" & FldName & "] Desc"
13

End If
14

'若是第1页则无须复杂的语句
16

If PageIndex = 1 Then
17

StrTemp = ""
18

If StrWhere <> "" Then
19

Strtmp = " Where " & StrWhere
20

End If
21

StrSql = "Select Top " & PageSize & " * From [" & TblName & "]" & Strtmp & StrOrder
22

Else '若不是第1页，构造sql语句
23

StrSql = "Select Top " & PageSize & " * From [" & TblName & "] Where [" & FldName & "]" & StrTemp & _
24

" From (Select Top " & (PageIndex-1) * PageSize & " [" & FldName & "] From [" & TblName & "]"
25

If StrWhere <> "" Then
26

StrSql = StrSql & " Where " & StrWhere
27

End If
28

StrSql = StrSql & StrOrder & ") As Tbltemp)"
29

If StrWhere <> "" Then
30

StrSql = StrSql & " And " & StrWhere
31

End If
32

StrSql = StrSql & StrOrder
33

End If
34

GetPageSql = StrSql '返回sql语句
35

End Function
36

Response.Write(GetPageSql("Message","MessageID",10,1,0,""))
37

【收藏】【评论】【推荐】【投稿】【打印】【关闭】