SELECT TOP [页大小] [你需要显示的列] FROM [表名] WHERE [主键或者带有唯一约束的字段或者加有聚集索引的信息(后面使用XXX代替)] > ( SELECT MAX([表的别名].[XXX]) FROM ( SELECT [XXX] FROM Tab ORDER BY [XXX] ) as [表的别名] ) ORDER BY [XXX]
不好意思 少写了一部分 select top 10 * from Tab where uid >( select max(a.uid) from ( select top 20 uid from Tab order by uid ) as a ) order by uidSELECT TOP [页大小] [你需要显示的列] FROM [表名] WHERE [主键或者带有唯一约束的字段或者加有聚集索引的信息(后面使用XXX代替)] > ( SELECT MAX([表的别名].[XXX]) FROM ( SELECT TOP [页大小]*[页数] [XXX] FROM Tab ORDER BY [XXX] ) as [表的别名] ) ORDER BY [XXX]
CREATE PROC SP_Theme_Query @SearchCondition VARCHAR(2000), --查询条件 @OrderList VARCHAR(1000), --排序列表 @PageSize INT=10, --每页的大小 @PageIndex INT --要显示的页码 AS BEGIN SET NOCOUNT ON DECLARE @p1 int DECLARE @SQL VARCHAR(8000) DECLARE @PageCount INT IF ISNULL(@SearchCondition,'')<>'' AND ISNULL(@OrderList,'')='' BEGIN SET @SQL='SELECT * FROM Theme WHERE '+@SearchCondition+' ' END IF ISNULL(@SearchCondition,'')='' AND ISNULL(@OrderList,'')<>'' BEGIN SET @SQL='SELECT * FROM Theme ORDER BY '+@OrderList+' ' END IF ISNULL(@SearchCondition,'')<>'' AND ISNULL(@OrderList,'')<>'' BEGIN SET @SQL='SELECT * FROM Theme WHERE '+@SearchCondition+' ORDER BY '+@OrderList+' ' END IF ISNULL(@SearchCondition,'')='' AND ISNULL(@OrderList,'')='' BEGIN SET @SQL='SELECT * FROM Theme ' END --初始化分页游标 EXEC sp_cursoropen @cursor=@p1 OUTPUT, @stmt=@SQL, @scrollopt=1, @ccopt=1, @rowcount=@PageCount OUTPUT --计算总页数 IF ISNULL(@PageSize,0)<1 SET @PageSize=10 SET @PageCount=(@PageCount+@PageSize-1)/@PageSize IF ISNULL(@PageIndex,0)<1 OR ISNULL(@PageIndex,0)>@PageCount SET @PageIndex=1 ELSE SET @PageIndex=(@PageIndex-1)*@PageSize+1 --显示指定页的数据 EXEC sp_cursorfetch @p1,16,@PageIndex,@PageSize --关闭分页游标 EXEC sp_cursorclose @p1 SET NOCOUNT OFF ENDGO 分页的
select max(a.uid) from (
select uid from Tab order by uid
) as a
)
order by uid
把问题描述清楚一点
使用数据控件的话可以把Autopage的属性设为true,然后设置pagesize,就可以了
SELECT MAX([表的别名].[XXX]) FROM (
SELECT [XXX] FROM Tab ORDER BY [XXX]
) as [表的别名]
)
ORDER BY [XXX]
少写了一部分
select top 10 * from Tab where uid >(
select max(a.uid) from (
select top 20 uid from Tab order by uid
) as a
)
order by uidSELECT TOP [页大小] [你需要显示的列] FROM [表名] WHERE [主键或者带有唯一约束的字段或者加有聚集索引的信息(后面使用XXX代替)] > (
SELECT MAX([表的别名].[XXX]) FROM (
SELECT TOP [页大小]*[页数] [XXX] FROM Tab ORDER BY [XXX]
) as [表的别名]
)
ORDER BY [XXX]
或者lz你通过存储过程自己分页显示,每次取回一个页面的数据,点击下一页就取下一页数据,这样的话在表比较大时,性能会高。
在 SelectedINdexChanging事件里面写了
Gridview1.PageIndex=e.NewPageIndex;
databind();//你绑定数据grid的方法
@SearchCondition VARCHAR(2000), --查询条件
@OrderList VARCHAR(1000), --排序列表
@PageSize INT=10, --每页的大小
@PageIndex INT --要显示的页码
AS
BEGIN
SET NOCOUNT ON
DECLARE @p1 int
DECLARE @SQL VARCHAR(8000)
DECLARE @PageCount INT
IF ISNULL(@SearchCondition,'')<>'' AND ISNULL(@OrderList,'')=''
BEGIN
SET @SQL='SELECT * FROM Theme WHERE '+@SearchCondition+' '
END
IF ISNULL(@SearchCondition,'')='' AND ISNULL(@OrderList,'')<>''
BEGIN
SET @SQL='SELECT * FROM Theme ORDER BY '+@OrderList+' '
END
IF ISNULL(@SearchCondition,'')<>'' AND ISNULL(@OrderList,'')<>''
BEGIN
SET @SQL='SELECT * FROM Theme WHERE '+@SearchCondition+' ORDER BY '+@OrderList+' '
END
IF ISNULL(@SearchCondition,'')='' AND ISNULL(@OrderList,'')=''
BEGIN
SET @SQL='SELECT * FROM Theme '
END
--初始化分页游标
EXEC sp_cursoropen
@cursor=@p1 OUTPUT,
@stmt=@SQL,
@scrollopt=1,
@ccopt=1,
@rowcount=@PageCount OUTPUT
--计算总页数
IF ISNULL(@PageSize,0)<1
SET @PageSize=10
SET @PageCount=(@PageCount+@PageSize-1)/@PageSize
IF ISNULL(@PageIndex,0)<1 OR ISNULL(@PageIndex,0)>@PageCount
SET @PageIndex=1
ELSE
SET @PageIndex=(@PageIndex-1)*@PageSize+1
--显示指定页的数据
EXEC sp_cursorfetch @p1,16,@PageIndex,@PageSize
--关闭分页游标
EXEC sp_cursorclose @p1
SET NOCOUNT OFF
ENDGO
分页的