Question

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

数据量>1W，字段有几十个。 id(主键)，name（姓名varchar），生日（日期date)

现在用
select top 每页数量 * not in （select top 页数*每页数量 id from 表 order by id）order by id

not in 效率低，1W数据根本动不了，加索引也不行

那位高手赐教一下，解决了，加分，等级信誉保证
上面sql语句写错了：
select top 每页数量 * from 表 where id not in （select top 页数*每页数量 id from 表 order by id）order by id

若需要排序的字段是两个呢。max，min就不好用了吧。
因为真正要排序的是 name，可能为空，所以排序字段是
order by id asc, name asc

Answer 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 <(此处根据顺序和逆序)

Answer 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,""))
%>

Answer 3

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

Answer 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