--假如 每个页显示10条记录,我点第四页的话,可以很轻松的用下面的sql来实现
select top 10 * from T1
where id not in(select top 30 id from T1 order by id)
order by id--但问题是,现实中不可能就一个表这么简单,可能会有多个表相关联
select top 10 * from T1
Left outer join T2 on T1.id=T2.id
Left outer join T3 on T2.id=T3.id
where --一大堆查询条件
and T1.id not in(select top 30 --就不知道这里怎么写了,)
order by id
--我想了个办法,使用临时表,不知道是否可行
select top 10 *
into #temp
from T1
Left outer join T2 on T1.id=T2.id
Left outer join T3 on T2.id=T3.id
where --一大堆查询条件
order by id--分页代码
select top 10 * from #temp
where id not in(select top 30 id from #temp order by id)
order by id
(
@pageSize int,
@startIndex int,
@orderByString varchar(200)
)
as
declare @sql varchar(8000)
set @sql = ''
begin
select @sql = @sql + 'select top '
select @sql = @sql + ''+cast(@pageSize as varchar(10))+'' + ' * from (select * from ItemData) A where convert(varchar(10),A.date ,101)+A.iCode+A.itCode+A.cCode+A.pCode Not In '
select @sql = @sql + ' (select top '+cast(@startIndex as varchar(10))+' convert(varchar(10),date ,101)+iCode+itCode+cCode+pCode from ItemData)'
select @sql = @sql + ' order by '+@orderByString+'' end
exec (@sql)
在分页用到top时where里不要用id not in...,问题还是3#说的,有一部分数据会在分页显示的时候丢失,你用的SQL2005就用排序函数,把查询出来的结果集作一个自然排序 row_number() over (order by getdate()) ii, 然后用TOP时根据 ii 这个字段的值来走。
select --查询的字段名
from(
select *,row_number() over (order by getdate())ii from T1
Left outer join T2 on T1.id=T2.id
Left outer join T3 on T2.id=T3.id
where --一大堆查询条件
order by id
)U
where ii between ... and ... -- 以10条分页,第三页就是 30 到 39