http://community.csdn.net/Expert/topic/3292/3292678.xml?temp=.2602045
这个帖子里，
lihonggen0(李洪根，MS MVP，标准答案来了) 说用Select top 10 * from table where id>200这个方法较好，请问谁能给个用这个方法分页的例子。用存储过程分页应该返回什么值？谢谢
这个帖子里，
lihonggen0(李洪根，MS MVP，标准答案来了) 说用Select top 10 * from table where id>200这个方法较好，请问谁能给个用这个方法分页的例子。用存储过程分页应该返回什么值？谢谢
--测试示例
declare @pages int
select identity(int,1,1) id,getdate() dt,xx=cast('' as varchar(10)) into #t
from sysobjects
update #t set dt=dateadd(day,id-200,dt),
xx='xxxx'+right('000000'+cast(id as varchar(10)),6)exec sp_page '#t','id',0,0,'*',10,2,'',@pages output--按id顺序取第二页
exec sp_page '#t','id',0,1,'*',10,2,'',@pages output--按id倒序取第二页
exec sp_page '#t','xx',1,0,'*',10,3,'',@pages output--按xx顺序取第三页
exec sp_page '#t','xx',1,1,'*',10,3,'',@pages output--按xx倒序取第三页
exec sp_page '#t','dt',2,0,'*',10,2,'',@pages output--按dt顺序取第二页
exec sp_page '#t','dt',2,1,'*',10,2,'',@pages output--按dt倒序取第二页select 总页数=@pagesdrop table #tdeclare @pages int
exec up_page 'LD_SMDateItem','LocationCode',1,1,'BillNo,ProdCode,StoreCode,LocationCode',10,1,"BillNo='SM2004120100005' and ProdCode like '%%'",@pages output
select 总页数=@pages*/CREATE PROCEDURE up_page
@tb varchar(50), --表名
@col varchar(50), --按该列来进行分页
--@coltype int, --@col列的类型,0-数字类型,1-字符类型,2-日期时间类型
@orderby varchar(1), --排序,0-顺序,1-倒序
@ordercol varchar(100),--排序列
@collist varchar(800),--要查询出的字段列表,*表示全部字段
@pagesize int, --每页记录数
@page int, --指定页
@condition varchar(800)--查询条件
-- @pages int OUTPUT --总页数
AS
/*
功能描述：对指定表中满足条件的记录按指定列进行分页查询，分页可以顺序、倒序
查询可以指定页大小、指定查询任意页、指定输出字段列表，返回总页数
*/
DECLARE @sql nvarchar(4000),@where1 varchar(800),@where2 varchar(800),@pages varchar(50)
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 @pages=CEILING((COUNT(*)+0.0)/'+CAST(@pagesize AS varchar)+
-- ') FROM '+@tb+@where2
--EXEC sp_executesql @sql,N'@pages int OUTPUT',@pages OUTPUT--计算总页数SET @sql='SELECT @pages=COUNT(*) FROM '+@tb+@where2
EXEC sp_executesql @sql,N'@pages int OUTPUT',@pages OUTPUT--计算总页数
IF @orderby=0
SET @sql='SELECT TOP '+CAST(@pagesize AS varchar)+' '+@collist+ ','+ @pages+' Records '+
' FROM '+@tb+@where1+@col+'>(SELECT MAX('+@col+') '+
' FROM (SELECT TOP '+CAST(@pagesize*(@page-1) AS varchar)+' '+
@col+' FROM '+@tb+@where2+'ORDER BY '+@col+') t) ORDER BY '+@ordercol
ELSE
SET @sql='SELECT TOP '+CAST(@pagesize AS varchar)+' '+@collist+ ','+ @pages+' Records '+
' FROM '+@tb+@where1+@col+'<(SELECT MIN('+@col+') '+
' FROM (SELECT TOP '+CAST(@pagesize*(@page-1) AS varchar)+' '+
@col+' FROM '+@tb+@where2+'ORDER BY '+@col+' DESC) t) ORDER BY '+
@ordercol+' DESC'
IF @page=1--第一页
SET @sql='SELECT TOP '+CAST(@pagesize AS varchar)+' '+@collist+', '+@pages+' Records FROM '+@tb+
@where2+'ORDER BY '+@ordercol+CASE @orderby WHEN 0 THEN '' ELSE ' DESC' END
print @sql
EXEC(@sql)
GO
select top 10 * from A where id > (select max(id) from (select top 30 id from A )as A)
这样就可以的
坐车回家的时候我也在想这个问题，不过不知道sql语句怎么写，谢谢。
这样写会不会分页越往后，速度就越慢？(因为取出max(id)的结果集越来越大）
有改进的方法吗？
http://www.codeproject.com/aspnet/PagingLarge.aspif exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Paging_RowCount]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[Paging_RowCount]
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE PROCEDURE Paging_RowCount
(
@Tables varchar(1000),
@PK varchar(100),
@Sort varchar(200) = NULL,
@PageNumber int = 1,
@PageSize int = 10,
@Fields varchar(1000) = '*',
@Filter varchar(1000) = NULL,
@Group varchar(1000) = NULL)
AS
/*Default Sorting*/
IF @Sort IS NULL OR @Sort = ''
SET @Sort = @PK
/*Find the @PK type*/
DECLARE @SortTable varchar(100)
DECLARE @SortName varchar(100)
DECLARE @strSortColumn varchar(200)
DECLARE @operator char(2)
DECLARE @type varchar(100)
DECLARE @prec int
/*Set sorting variables.*/
IF CHARINDEX('DESC',@Sort)>0
BEGIN
SET @strSortColumn = REPLACE(@Sort, 'DESC', '')
SET @operator = '<='
END
ELSE
BEGIN
IF CHARINDEX('ASC', @Sort) = 0
SET @strSortColumn = REPLACE(@Sort, 'ASC', '')
SET @operator = '>='
END
IF CHARINDEX('.', @strSortColumn) > 0
BEGIN
SET @SortTable = SUBSTRING(@strSortColumn, 0, CHARINDEX('.',@strSortColumn))
SET @SortName = SUBSTRING(@strSortColumn, CHARINDEX('.',@strSortColumn) + 1, LEN(@strSortColumn))
END
ELSE
BEGIN
SET @SortTable = @Tables
SET @SortName = @strSortColumn
END
SELECT @type=t.name, @prec=c.prec
FROM sysobjects o
JOIN syscolumns c on o.id=c.id
JOIN systypes t on c.xusertype=t.xusertype
WHERE o.name = @SortTable AND c.name = @SortName
IF CHARINDEX('char', @type) > 0
SET @type = @type + '(' + CAST(@prec AS varchar) + ')'
DECLARE @strPageSize varchar(50)
DECLARE @strStartRow varchar(50)
DECLARE @strFilter varchar(1000)
DECLARE @strSimpleFilter varchar(1000)
DECLARE @strGroup varchar(1000)
/*Default Page Number*/
IF @PageNumber < 1
SET @PageNumber = 1
/*Set paging variables.*/
SET @strPageSize = CAST(@PageSize AS varchar(50))
SET @strStartRow = CAST(((@PageNumber - 1)*@PageSize + 1) AS varchar(50))
/*Set filter & group variables.*/
IF @Filter IS NOT NULL AND @Filter != ''
BEGIN
SET @strFilter = ' WHERE ' + @Filter + ' '
SET @strSimpleFilter = ' AND ' + @Filter + ' '
END
ELSE
BEGIN
SET @strSimpleFilter = ''
SET @strFilter = ''
END
IF @Group IS NOT NULL AND @Group != ''
SET @strGroup = ' GROUP BY ' + @Group + ' '
ELSE
SET @strGroup = ''
/*Execute dynamic query*/
EXEC(
'
DECLARE @SortColumn ' + @type + '
SET ROWCOUNT ' + @strStartRow + '
SELECT @SortColumn=' + @strSortColumn + ' FROM ' + @Tables + @strFilter + ' ' + @strGroup + ' ORDER BY ' + @Sort + '
SET ROWCOUNT ' + @strPageSize + '
SELECT ' + @Fields + ' FROM ' + @Tables + ' WHERE ' + @strSortColumn + @operator + ' @SortColumn ' + @strSimpleFilter + ' ' + @strGroup + ' ORDER BY ' + @Sort + '
'
)
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO