请教关于SQL语句分页的问题

HDHCMS(建网站+客户管理)系统 2013-12-10 08:44:00

以下是查询第二页的数据：
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 a.id 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 Order By a.DonforUserVip Desc,a.DonforUserYhjf Desc,a.DonforUserJcSj Desc)) Order By a.DonforUserVip Desc,a.DonforUserYhjf Desc,a.DonforUserJcSj Desc

以下是查询第三页的数据：
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 30 a.id 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 Order By a.DonforUserVip Desc,a.DonforUserYhjf Desc,a.DonforUserJcSj Desc)) Order By a.DonforUserVip Desc,a.DonforUserYhjf Desc,a.DonforUserJcSj Desc

每页显示10条

现在按上面的操作第二页会重复第一页的数据，第三页会重复第二页的数据······

我知道是中间“a.id not in”后面的查询语句没加“distinct”，但是加上这个又出现ORDER BY 后台的数据必须要在查询语句，如果查询语句加上这些参数，a.id又无法比较，头痛啊

请教各位如何解决这个问题好，谢谢

...全文

215 8 打赏收藏转发到动态举报

写回复

用AI写文章

8 条回复

切换为时间正序

请发表友善的回复…

发表回复

HDHCMS(建网站+客户管理)系统 2013-12-15

打赏
举报

引用 7 楼 yupeigu 的回复:

[quote=引用 6 楼 KenBest 的回复:] 不是忘记结贴了，想了解一下，还有没有更好的解决方法，呵呵。

如果你的系统是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

[/quote] 谢谢，我用的还是SQL2000，不过迟早会用上2000以上的版本

HDHCMS(建网站+客户管理)系统 2013-12-10

打赏
举报

引用 4 楼 onkey1999 的回复:

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 增加一个查询嵌套

非常感谢楼上的兄弟，经调试高度OK

onkey1999 2013-12-10

打赏
举报

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 增加一个查询嵌套

Yole 2013-12-10

打赏
举报

引用 2 楼 KenBest 的回复:

[quote=引用 1 楼 u010192842 的回复:] [quote=引用楼主 KenBest 的回复:] 以下是查询第二页的数据： 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 a.id 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 Order By a.DonforUserVip Desc,a.DonforUserYhjf Desc,a.DonforUserJcSj Desc)) Order By a.DonforUserVip Desc,a.DonforUserYhjf Desc,a.DonforUserJcSj Desc 以下是查询第三页的数据： 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 30 a.id 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 Order By a.DonforUserVip Desc,a.DonforUserYhjf Desc,a.DonforUserJcSj Desc)) Order By a.DonforUserVip Desc,a.DonforUserYhjf Desc,a.DonforUserJcSj Desc 每页显示10条现在按上面的操作第二页会重复第一页的数据，第三页会重复第二页的数据······ 我知道是中间“a.id not in”后面的查询语句没加“distinct”，但是加上这个又出现ORDER BY 后台的数据必须要在查询语句，如果查询语句加上这些参数，a.id又无法比较，头痛啊请教各位如何解决这个问题好，谢谢

价格rownumber() over() 行号然后用这个进行分页。你那种分页有重复数据，分页条件不能用带重复数据的字段。[/quote] 感谢楼上的朋友，现在用的数据库是SQL2000的[/quote]


 /*-------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

HDHCMS(建网站+客户管理)系统 2013-12-10

打赏
举报

引用 1 楼 u010192842 的回复:

[quote=引用楼主 KenBest 的回复:] 以下是查询第二页的数据： 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 a.id 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 Order By a.DonforUserVip Desc,a.DonforUserYhjf Desc,a.DonforUserJcSj Desc)) Order By a.DonforUserVip Desc,a.DonforUserYhjf Desc,a.DonforUserJcSj Desc 以下是查询第三页的数据： 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 30 a.id 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 Order By a.DonforUserVip Desc,a.DonforUserYhjf Desc,a.DonforUserJcSj Desc)) Order By a.DonforUserVip Desc,a.DonforUserYhjf Desc,a.DonforUserJcSj Desc 每页显示10条现在按上面的操作第二页会重复第一页的数据，第三页会重复第二页的数据······ 我知道是中间“a.id not in”后面的查询语句没加“distinct”，但是加上这个又出现ORDER BY 后台的数据必须要在查询语句，如果查询语句加上这些参数，a.id又无法比较，头痛啊请教各位如何解决这个问题好，谢谢

价格rownumber() over() 行号然后用这个进行分页。你那种分页有重复数据，分页条件不能用带重复数据的字段。[/quote] 感谢楼上的朋友，现在用的数据库是SQL2000的

Yole 2013-12-10

打赏
举报

引用楼主 KenBest 的回复:

以下是查询第二页的数据： 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 a.id 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 Order By a.DonforUserVip Desc,a.DonforUserYhjf Desc,a.DonforUserJcSj Desc)) Order By a.DonforUserVip Desc,a.DonforUserYhjf Desc,a.DonforUserJcSj Desc 以下是查询第三页的数据： 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 30 a.id 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 Order By a.DonforUserVip Desc,a.DonforUserYhjf Desc,a.DonforUserJcSj Desc)) Order By a.DonforUserVip Desc,a.DonforUserYhjf Desc,a.DonforUserJcSj Desc 每页显示10条现在按上面的操作第二页会重复第一页的数据，第三页会重复第二页的数据······ 我知道是中间“a.id not in”后面的查询语句没加“distinct”，但是加上这个又出现ORDER BY 后台的数据必须要在查询语句，如果查询语句加上这些参数，a.id又无法比较，头痛啊请教各位如何解决这个问题好，谢谢

价格rownumber() over() 行号然后用这个进行分页。你那种分页有重复数据，分页条件不能用带重复数据的字段。

LongRui888 2013-12-10

打赏
举报

引用 6 楼 KenBest 的回复:

不是忘记结贴了，想了解一下，还有没有更好的解决方法，呵呵。

如果你的系统是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

HDHCMS(建网站+客户管理)系统 2013-12-10