你这个分页不是很麻烦,数据有一定的排序顺序
你可以在存储过程中设一个参数@y,用他判断取的页数
creat procedure fy
@y int
as
declare @b int
declare @e int
declare @sql varchar(8000)
set @sql = 'select @b=max(bbbid)
from (select top '+cast(@y as varchar(10))+' bbbid from tblbbb) a'
exec sp_executesql @sql ,'@b int output',@b output
set @sql = 'select @e=max(bbbid)
from (select top '+cast((@y-2+1) as varchar(10)) +' bbbid from tblbbb) a'
exec sp_executesql @sql ,'@e int output',@e output select a.aaaId,a.aaaName,a.aaamemo,b.bbbid,b.bbbOrder
from tblaaa a inner join tblbbb b on a.aaaid=b.bbbid
where b.bbbid between @b and @e
go
你可以在存储过程中设一个参数@y,用他判断取的页数
creat procedure fy
@y int
as
declare @b int
declare @e int
declare @sql varchar(8000)
set @sql = 'select @b=max(bbbid)
from (select top '+cast(@y as varchar(10))+' bbbid from tblbbb) a'
exec sp_executesql @sql ,'@b int output',@b output
set @sql = 'select @e=max(bbbid)
from (select top '+cast((@y-2+1) as varchar(10)) +' bbbid from tblbbb) a'
exec sp_executesql @sql ,'@e int output',@e output select a.aaaId,a.aaaName,a.aaamemo,b.bbbid,b.bbbOrder
from tblaaa a inner join tblbbb b on a.aaaid=b.bbbid
where b.bbbid between @b and @e
go
要知道,如果tblbbb有100万行,tblaaa有500万行
一次返回给应用程序,会死机的!!!
page 当前页数 (1,2...)
string sql = "SELECT TOP " + pageSize + " * FROM tblaaa t1 INNER JOIN tblbbb t2 ON t1.aaaID = t2.bbbID WHERE t1.aaaID NOT IN (SELECT TOP " + (page-1)*pageSize + " t1.aaaID FROM tblaaa t1 INNER JOIN tblbbb t2 ON t1.aaaID = t2.bbbID ORDER BY t1.aaaID) ORDER BY t1.aaaID