Question

sql语句分页和排序问题

一般的分页语句是 select top "+pageSize+" * from 表 where Id not in (select top "+(pageNow-1)*pageSize+" Id from 表)

如果带排序比如order by 某字段，的分页语句怎么写？谢谢

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

Answer 2

select aa.字段 from (select top "+pageSize+" * from 表 where Id not in (select top "+(pageNow-1)*pageSize+" Id from 表) aa order by aa.要排序的字段