Access数据库分页查询，效率sql语句

2025-01-05 16:53:15

推荐回答（4个）

回答（1）：

in的效率太低，不能利用索引，建议使用：
select top 每页数量 * from 表 where id >(select top 1 max(id) from (select top （页数-1）*每页数量 from 表 order by id,name)) 或
select top 每页数量 * from 表 where id <(此处根据顺序和逆序)

回答（2）：

<%
'分页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 & Str Order
End If
GetPageSql = StrSql '返回sql语句
End Function
Response.Write(GetPageSql("Message","MessageID",10,1,0,""))
%>

回答（3）：

select * from `user_info` where create_by='1' and deleteflag=0 limit 0,5
这就是sql的分页了

回答（4）：

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