SQL分页

最新推荐文章于 2024-11-22 10:11:53 发布

转载最新推荐文章于 2024-11-22 10:11:53 发布 · 480 阅读

/*@table varchar(2000), --表名
@col varchar(50), --按该列来进行分页
@orderby bit, --排序,0-顺序,1-倒序
@collist varchar(800),--要查询出的字段列表,*表示全部字段
@pagesize int, --每页记录数
@page int, --指定页
@condition varchar(800) --查询条件
*/

DECLARE @sql nvarchar(4000),@where1 varchar(800),@where2 varchar(800),
@total_Item int,@total_Page int ,@pagesize int,@page int,@condition varchar(800),@orderby bit,
@col varchar(50),@table varchar(2000),@collist varchar(800)

/*IF @condition is null or rtrim(@condition)=''
BEGIN--没有查询条件
SET @where1=' WHERE '
SET @where2=' '
END
ELSE
BEGIN--有查询条件
SET @where1=' WHERE ('+@condition+') AND '--本来有条件再加上此条件
SET @where2=' WHERE ('+@condition+') '--原本没有条件而加上此条件
END
*/

SET @sql='SELECT @total_Item=CEILING((COUNT(*)+0.0)'+') FROM PT_HOMEWORK'
SET @pagesize =6
SET @page =2
SET @orderby =0
EXEC sp_executesql @sql,N'@total_Item int OUTPUT',@total_Item OUTPUT
set @total_Page = Ceiling((@total_Item+0.0)/@pagesize) --计算页总数
print @total_Page

IF @orderby=0
SET @sql='SELECT TOP
'+CAST(@pagesize AS varchar)+' *
FROM PT_HOMEWORK
WHERE HOMEWORK_CD > (
    SELECT MAX(HOMEWORK_CD)
    FROM (
     SELECT TOP '+CAST(@pagesize*(@page-1) AS varchar)+'HOMEWORK_CD
     FROM PT_HOMEWORK
     ORDER BY HOMEWORK_CD
     ) t
  ) ORDER BY HOMEWORK_CD'
ELSE
SET @sql='SELECT TOP '+CAST(@pagesize AS varchar)+' *
FROM PT_HOMEWORK
WHERE HOMEWORK_CD >(
   SELECT MIN(HOMEWORK_CD)
   FROM (
     SELECT TOP '+CAST(@pagesize*(@page-1) AS varchar)+'
     HOMEWORK_CD
     FROM PT_HOMEWORK
     ORDER BY HOMEWORK_CD DESC
    ) t
  ) ORDER BY HOMEWORK_CD DESC'
IF @page=1--第一页
SET @sql='SELECT TOP '+CAST(@pagesize AS varchar)+' *
FROM PT_HOMEWORK
ORDER BY HOMEWORK_CD '+CASE @orderby WHEN 0 THEN '' ELSE ' DESC' END
--print @sql
EXEC(@sql)