价格rownumber() over() 行号 然后用这个进行分页。 你那种分页有重复数据,分页条件不能用带重复数据的字段。感谢楼上的朋友,现在用的数据库是SQL2000的 /*-------Microsoft SQL Server 2000 Pagination-------*/ CREATE PROCEDURE MsSql2KPagination ( @Columns NVARCHAR(500), --数据库中相关表表列 @TbNames NVARCHAR(200), --数据库中相关表表名 @WhereCondition NVARCHAR(1500), --Where条件语句,不含有Where关键字 @OrderColumns NVARCHAR(350), --排序列名称,支持多列排序,例如ORDER BY column1,column2但是语句中不能还有ORDER BY关键字 @IsOrderByASC BIT, --排序方式1:ASC,0:DESC @KeyWord NVARCHAR(100), --很关键,可以是上述数据库表列的一个列名,主要用于按某一列排序,建议开始使用时是主键列名 @CurrentPageIndex INT, --当前分页页面数,如果程序是第一次使用则该值为1 @PageSize INT, --程序需求每页显示的数据条数 @TotalPages INT OUTPUT, --数据库中总的页面数量 @TotalRecords INT OUTPUT --数据库中总的记录数量 ) AS SET NOCOUNT ON --------设置WHERE条件-------- BEGIN DECLARE @WHERE NVARCHAR(2000) DECLARE @OTHERWHERE NVARCHAR(2000) IF ISNULL(@WhereCondition,'') = '' BEGIN SET @WHERE = '' SET @OTHERWHERE = ' WHERE ' END ELSE BEGIN SET @WHERE = ' WHERE ' + @WhereCondition SET @OTHERWHERE = ' WHERE ' + @WhereCondition + ' AND ' END END --------设置ORDER BY条件-------- BEGIN DECLARE @ORDERBY NVARCHAR(800) IF ISNULL(@OrderColumns,'') = '' SET @ORDERBY = '' ELSE BEGIN IF @IsOrderByASC = 1 SET @ORDERBY = ' ORDER BY ' + @OrderColumns + ' ASC ' ELSE SET @ORDERBY = ' ORDER BY ' + @OrderColumns + ' DESC ' END END --------计算分页页面数量和总数据条数-------- BEGIN DECLARE @_PageNum NVARCHAR(1000) SET @_PageNum = 'SELECT @TotalRecords=COUNT(*), @TotalPages=CEILING((COUNT(*)+0.0/' + CAST(@PageSize AS VARCHAR) + ') FROM ' + @TbNames + @WHERE EXEC SP_EXECUTESQL @_PageNum,N'@TotalRecords INT OUTPUT,@TotalPages INT OUTPUT', @TotalRecords OUTPUT,@TotalPages OUTPUT END --------现在开始实行分页-------- BEGIN DECLARE @_PAGESQL NVARCHAR(5000) IF @CurrentPageIndex = 1 SET @_PAGESQL = 'SELECT TOP ' + STR(@PageSize) + ' ' + @Columns + ' FROM ' + @TbNames + @WHERE + @ORDERBY ELSE BEGIN IF @IsOrderByASC = 1 SET @_PAGESQL = 'SELECT TOP ' + STR(@PageSize) + ' ' + @Columns + ' FROM ' + @TbNames + @OTHERWHERE + @KeyWord + '>' + '(SELECT MAX(' + @KeyWord + ') FROM (SELECT TOP ' + STR(@PageSize*(@CurrentPageIndex-1)) + ' ' + @KeyWord + ' FROM ' + @TbNames + @WHERE + @ORDERBY + '))' + @ORDERBY ELSE SET @_PAGESQL = 'SELECT TOP ' + STR(@PageSize) + ' ' + @Columns + ' FROM ' + @TbNames + @OTHERWHERE + @KeyWord + '<' + '(SELECT MIN(' + @KeyWord + ') FROM (SELECT TOP ' + STR(@PageSize*(@CurrentPageIndex-1)) + ' ' + @KeyWord + ' FROM ' + @TbNames + @WHERE + @ORDERBY + '))' + @ORDERBY END EXEC(@_PAGESQL) END GO
select distinct Top 10 a.id,a.DonforUserGsm,a.Name,a.DonforUserZycp,a.DonforUserDh,a.DonforUserYhjf,a.DonforUserSzs,a.DonforUserDjs,a.DonforUserVip,a.DonforUserQq,a.DonforUserVip,a.DonforUserYhjf,a.DonforUserJcSj from [user] a,Donfor_Xxfbb_Lb b,Donfor_Xxfbb_Lb c Where CharIndex(b.Lbmc,a.DonforUserZycp)>0 and b.Ssid=c.id and c.id=18 and (a.id Not in (select top 20 bb.id From (Select distinct a.id,a.DonforUserVip,a.DonforUserYhjf,a.DonforUserJcSj From [user] a,Donfor_Xxfbb_Lb b,Donfor_Xxfbb_Lb c Where CharIndex(b.Lbmc,a.DonforUserZycp)>0 and b.Ssid=c.id and c.id=18) as bb Order By bb.DonforUserVip Desc,bb.DonforUserYhjf Desc,bb.DonforUserJcSj Desc )) Order By a.DonforUserVip Desc,a.DonforUserYhjf Desc,a.DonforUserJcSj Desc增加一个查询嵌套
如果你的系统是2005及以上的话,建议用row_number函数来做分页,这样稍微要简单一点,给个代码,你参考一下哈:declare @page_size int; declare @page_num int;--比如:每页10条记录 set @page_size = 10;--比如:先取第1页 set @page_num = 1;select * from ( select *, row_number() over(order by @@servername) as rownum, --这里按照@@servername来排序, --你可以根据需要按照id,sid,sname等字段来排序 (row_number() over(order by @@servername) - 1) / @page_size as pagenum from sys.objects )t where pagenum = @page_num - 1
如果你的系统是2005及以上的话,建议用row_number函数来做分页,这样稍微要简单一点,给个代码,你参考一下哈:declare @page_size int; declare @page_num int;--比如:每页10条记录 set @page_size = 10;--比如:先取第1页 set @page_num = 1;select * from ( select *, row_number() over(order by @@servername) as rownum, --这里按照@@servername来排序, --你可以根据需要按照id,sid,sname等字段来排序 (row_number() over(order by @@servername) - 1) / @page_size as pagenum from sys.objects )t where pagenum = @page_num - 1
然后用这个进行分页。
你那种分页有重复数据,分页条件不能用带重复数据的字段。感谢楼上的朋友,现在用的数据库是SQL2000的
然后用这个进行分页。
你那种分页有重复数据,分页条件不能用带重复数据的字段。感谢楼上的朋友,现在用的数据库是SQL2000的 /*-------Microsoft SQL Server 2000 Pagination-------*/
CREATE PROCEDURE MsSql2KPagination
(
@Columns NVARCHAR(500), --数据库中相关表表列
@TbNames NVARCHAR(200), --数据库中相关表表名
@WhereCondition NVARCHAR(1500), --Where条件语句,不含有Where关键字
@OrderColumns NVARCHAR(350), --排序列名称,支持多列排序,例如ORDER BY column1,column2但是语句中不能还有ORDER BY关键字
@IsOrderByASC BIT, --排序方式1:ASC,0:DESC
@KeyWord NVARCHAR(100), --很关键,可以是上述数据库表列的一个列名,主要用于按某一列排序,建议开始使用时是主键列名
@CurrentPageIndex INT, --当前分页页面数,如果程序是第一次使用则该值为1
@PageSize INT, --程序需求每页显示的数据条数
@TotalPages INT OUTPUT, --数据库中总的页面数量
@TotalRecords INT OUTPUT --数据库中总的记录数量
)
AS
SET NOCOUNT ON
--------设置WHERE条件--------
BEGIN
DECLARE @WHERE NVARCHAR(2000)
DECLARE @OTHERWHERE NVARCHAR(2000)
IF ISNULL(@WhereCondition,'') = ''
BEGIN
SET @WHERE = ''
SET @OTHERWHERE = ' WHERE '
END
ELSE
BEGIN
SET @WHERE = ' WHERE ' + @WhereCondition
SET @OTHERWHERE = ' WHERE ' + @WhereCondition + ' AND '
END
END
--------设置ORDER BY条件--------
BEGIN
DECLARE @ORDERBY NVARCHAR(800)
IF ISNULL(@OrderColumns,'') = ''
SET @ORDERBY = ''
ELSE
BEGIN
IF @IsOrderByASC = 1
SET @ORDERBY = ' ORDER BY ' + @OrderColumns + ' ASC '
ELSE
SET @ORDERBY = ' ORDER BY ' + @OrderColumns + ' DESC '
END
END
--------计算分页页面数量和总数据条数--------
BEGIN
DECLARE @_PageNum NVARCHAR(1000)
SET @_PageNum = 'SELECT @TotalRecords=COUNT(*), @TotalPages=CEILING((COUNT(*)+0.0/'
+ CAST(@PageSize AS VARCHAR) + ') FROM ' + @TbNames + @WHERE
EXEC SP_EXECUTESQL @_PageNum,N'@TotalRecords INT OUTPUT,@TotalPages INT OUTPUT',
@TotalRecords OUTPUT,@TotalPages OUTPUT
END
--------现在开始实行分页--------
BEGIN
DECLARE @_PAGESQL NVARCHAR(5000) IF @CurrentPageIndex = 1
SET @_PAGESQL = 'SELECT TOP ' + STR(@PageSize) + ' ' + @Columns
+ ' FROM ' + @TbNames + @WHERE + @ORDERBY
ELSE
BEGIN
IF @IsOrderByASC = 1
SET @_PAGESQL = 'SELECT TOP ' + STR(@PageSize) + ' ' + @Columns
+ ' FROM ' + @TbNames + @OTHERWHERE + @KeyWord
+ '>' + '(SELECT MAX(' + @KeyWord + ') FROM (SELECT TOP '
+ STR(@PageSize*(@CurrentPageIndex-1)) + ' ' + @KeyWord
+ ' FROM ' + @TbNames + @WHERE + @ORDERBY + '))' + @ORDERBY
ELSE
SET @_PAGESQL = 'SELECT TOP ' + STR(@PageSize) + ' ' + @Columns
+ ' FROM ' + @TbNames + @OTHERWHERE + @KeyWord
+ '<' + '(SELECT MIN(' + @KeyWord + ') FROM (SELECT TOP '
+ STR(@PageSize*(@CurrentPageIndex-1)) + ' ' + @KeyWord
+ ' FROM ' + @TbNames + @WHERE + @ORDERBY + '))' + @ORDERBY
END
EXEC(@_PAGESQL)
END
GO
如果你的系统是2005及以上的话,建议用row_number函数来做分页,这样稍微要简单一点,给个代码,你参考一下哈:declare @page_size int;
declare @page_num int;--比如:每页10条记录
set @page_size = 10;--比如:先取第1页
set @page_num = 1;select *
from
(
select *,
row_number() over(order by @@servername) as rownum,
--这里按照@@servername来排序,
--你可以根据需要按照id,sid,sname等字段来排序
(row_number() over(order by @@servername) - 1) / @page_size as pagenum
from sys.objects
)t
where pagenum = @page_num - 1
如果你的系统是2005及以上的话,建议用row_number函数来做分页,这样稍微要简单一点,给个代码,你参考一下哈:declare @page_size int;
declare @page_num int;--比如:每页10条记录
set @page_size = 10;--比如:先取第1页
set @page_num = 1;select *
from
(
select *,
row_number() over(order by @@servername) as rownum,
--这里按照@@servername来排序,
--你可以根据需要按照id,sid,sname等字段来排序
(row_number() over(order by @@servername) - 1) / @page_size as pagenum
from sys.objects
)t
where pagenum = @page_num - 1
谢谢,我用的还是SQL2000,不过迟早会用上2000以上的版本