Question

请问SQL SERVER 怎样先从小到大排序然后再分页

select * from SData where Ll='1-2-1' order by length 本来用这个语句可以查询出所有数据并且排序 但是现在数据量太大 需要分页查询 请问怎么修改能够分页？ 每页需要查询600个数据 先从小到大排序然后再分页 我用的是SQL SERVER 2000

Answer 1

从小到大 是指日期还是别的什么参数?其属性是什么?
<% dim urll
urll="本页文件名"
Const MaxPerPage=600 '每页数量
dim totalPut
dim CurrentPage
dim TotalPages
dim j
dim sql
if Not isempty(SafeRequest("page",1)) then
currentPage=Cint(SafeRequest("page",1))
else
currentPage=1
end if
set rs = server.CreateObject("adodb.recordset")
rs.open "select * from SData where Ll='1-2-1' order by length",conn,1,1
if rs.eof And rs.bof then
Response.Write " 对不起，暂时还没有内容！"
else
totalPut=rs.recordcount
if currentpage<1 then
currentpage=1
end if
if (currentpage-1)*MaxPerPage>totalput then
if (totalPut mod MaxPerPage)=0 then
currentpage= totalPut \ MaxPerPage
else
currentpage= totalPut \ MaxPerPage + 1
end if
end if
if currentPage=1 then
showContent
showpage totalput,MaxPerPage,"'"&urll&"'"
else
if (currentPage-1)*MaxPerPage<totalPut then
rs.move (currentPage-1)*MaxPerPage
dim bookmark
bookmark=rs.bookmark
showContent
showpage totalput,MaxPerPage,"'"&urll&"'"
else
currentPage=1
showContent
showpage totalput,MaxPerPage,"'"&urll&"'"
end if
end if
end if
sub showContent
dim i
i=0
do while not rs.eof
Response.Write "输出部分"
i=i+1
if i>=MaxPerPage then Exit Do
rs.movenext
loop
rs.close
set rs=nothing
End Sub
Function showpage(totalnumber,maxperpage,filename)
Dim n
If totalnumber Mod maxperpage=0 Then
n= totalnumber \ maxperpage
Else
n= totalnumber \ maxperpage+1
End If %>
<form method=Post action= <% = filename %>>
<p align='center'>
<%
If CurrentPage<2 Then %>
首页 上页
<%
Else %>
<a class="F1" href=<% = filename %>?page=1>首页</a>
<a class="F1" href=<% = filename %>?page=<% = CurrentPage-1 %>>上页</a>
<%
End If
If n-currentpage<1 Then %>
下页 末页
<%
Else %>
<a class="F1" href=<% = filename %>?page=<% = (CurrentPage+1) %>>下页</a>
<a class="F1" href=<% = filename %>?page=<% = n %>>末页</a>
<%
End If %>
第 <b>
<% = CurrentPage %>
</b> 页 共 <b>
<% = n %>
</b> 页 共 <b>
<% = totalnumber %>
</b> 条　每页 <b>
<% = maxperpage %>
</b> 条 转到第：
<input type='text' name='page' size=2 maxlength=10 class=smallInput value=<% = currentpage %>>
页
<input type='submit' class='contents' value='跳转' name='cndok'>
</form>
<% End Function %>

Answer 2

select top 600 from table where id > (
select max(id) from (
select top (600*pageNum) id from table order by id
) innerTable
) outterTable

600*pageNum这个值需要你在程序里算好。就是页码*每页的600条

Answer 3

SELECT ROW_NUMBER OVER (ORDER BY LENGTH asc) AS RowNum, * from SData where Ll='1-2-1'
WHERE ROW_NUMBER >=1 AND ROW_NUMBER <= 600
即是第一页，以次类推