首页 新闻 赞助 找找看

net存储过程分页

0
悬赏园豆:10 [已关闭问题] 关闭于 2013-03-23 07:53

该功能的原先思路是对一个联合查询的结果进行分页,但是报错,显示如下错误:

消息 1033,级别 15,状态 1,过程 P_PSelectPage,第 26 行
除非另外还指定了 TOP 或 FOR XML,否则,ORDER BY 子句在视图、内联函数、派生表、子查询和公用表表达式中无效。

存储过程代码如下:

 

if object_id('P_PSelectPage') is not null

drop procedure P_PSelectPage

go

create procedure P_PSelectPage

           @G_Name nvarchar(15),--用户昵称

           @G_pageCount int ,--每一页要显示的行数

           @G_pageNum int,--第几页

           @G_numTotal int output --总共有多少条记录

as  

        declare @temp nvarchar(6)  

         declare @indexStart int  

         declare @indexEnd int

         set @indexStart = ((@G_pageNum-1) * @G_pageCount) + 1

         set @indexEnd= @indexStart + @G_pageCount  

         --根据用户名获取对应的用户ID  

         declare @ID int  select @ID=MID from tb_Member where MName = @G_Name

          --获取总的数据量

         set @G_numTotal = (select count(*) from tb_PersonalBook where MID = @ID)

         --根据对应的用户ID获取对应的用户记录

        select * from  

      (  

          select row_number() over (order by bookId asc) as rowNum ,* from   

          (    

                select  b.BID as bookId,b.BTitle as bookTtile,b.BAuthor as bookAuthor,b.BPublisher as publisher,b.BImg as img,   CONVERT(varchar(100),p.PDate, 20) as perDate       from tb_Book b inner join tb_PersonalBook p on b.BID=p.BID and p.MID=@ID order by p.PDate desc  

          )    as t where  rowNum >=@indexStart and rowNum <@indexEnd

       )

go

问该怎么改才能实现分页?

听雨读诗的主页 听雨读诗 | 初学一级 | 园豆:47
提问于:2013-03-22 09:06
< >
分享
所有回答(4)
0

问题解决了,正如报错信息所言,加上top就可以了:如下:

select * from
 (
  select row_number() over (order by bookId asc) as rowNum ,* from
   (
    select top 100 b.BID as bookId,b.BTitle as bookTtile,b.BAuthor as bookAuthor,b.BPublisher as publisher,b.BImg as img,
      CONVERT(varchar(100),p.PDate, 20) as perDate
      from tb_Book b inner join tb_PersonalBook p on b.BID=p.BID and p.MID=@ID order by p.PDate desc
   )as tb
   
 )as t where  rowNum >=@indexStart and rowNum <@indexEnd

听雨读诗 | 园豆:47 (初学一级) | 2013-03-22 09:15

top 100 percent 

支持(0) 反对(0) 飞来飞去 | 园豆:2057 (老鸟四级) | 2013-03-22 09:17

select * from
(
  select row_number() over (order by bookId asc) as rowNum ,* from
   (
    select top 100 percent b.BID as bookId,b.BTitle as bookTtile,b.BAuthor as bookAuthor,b.BPublisher as publisher,b.BImg as img,
      CONVERT(varchar(100),p.PDate, 20) as perDate
      from tb_Book b inner join tb_PersonalBook p on b.BID=p.BID and p.MID=@ID order by p.PDate desc
   )as tb
  
)as t where  rowNum >=@indexStart and rowNum <@indexEnd

意思就是对查询出来的tb表进行增加一个序号操作,然后在最外层对t表进行按序号间断取页操作。too 100 percent 意思是获取所有100%合适的联合查询结果

支持(0) 反对(0) 听雨读诗 | 园豆:47 (初学一级) | 2013-03-22 09:33
0

如果是2005以上的版本,不需要这么麻烦做分页

http://www.cnblogs.com/anjou/archive/2007/10/17/926944.html

陈希章 | 园豆:2538 (老鸟四级) | 2013-03-22 09:29
0

要是查询带where子句的话,肿么办。。。

羽商宫 | 园豆:2490 (老鸟四级) | 2013-03-22 09:40

一样的,我这里是联合连个表进行查询采用on,这联合查询的on 和单表查询的where是一样的

支持(0) 反对(0) 听雨读诗 | 园豆:47 (初学一级) | 2013-03-22 09:42

@听雨读诗: 下面是我写的另外一个查询,就是用到where了:

select BID as id, BTitle as title,BImg as img,BAuthor as author from
 (

select row_number() over (order by BID asc) as rowNum , * from tb_Book where  BTitle like '%'+@G_keyWord+'%'

)
  as t where rowNum >=@indexStart and rowNum <@indexEnd

支持(0) 反对(0) 听雨读诗 | 园豆:47 (初学一级) | 2013-03-22 09:43

@听雨读诗: 哦哦,谢谢了,真不好意思,你提问。。我还问你问题。。。

支持(0) 反对(0) 羽商宫 | 园豆:2490 (老鸟四级) | 2013-03-22 10:18
0

恭喜,解决了

jerry-Tom | 园豆:4077 (老鸟四级) | 2013-03-22 09:58
清除回答草稿
   您需要登录以后才能回答,未注册用户请先注册