Question

Sql 分页与排序语句

"select top 5 * from Table1 where id not in(select top 5*x id from Table1) order by id desc";分页没问题，假设，我要排序，问题就出来了，假设有5页，每页都是5条数据，不加排序，正常，加了每页显示都是倒数的5条数据.该怎么修改

Answer 1

create proc sp_page -- 数据库名，表名，字段名，第几页，每页行数
(@dataname varchar(20),@tablename varchar(20) ,@fieldname varchar(20),@page_cur int,@page_size int)
as
declare @sqlstr varchar(200)
set @sqlstr='select top '+cast( @page_size as varchar(20))+' * from '
+@dataname+'..'+@tablename+' where '+@fieldname+' not in ( select top '
+cast((@page_cur-1)*@page_size as varchar(20))+' '+@fieldname+' from '
+@dataname+'..'+@tablename+' order by '+@tablename+'.'@fieldname')'
execute(@sqlstr)

go

--exec sp_page pubs,jobs,job_id,3,2