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
问该怎么改才能实现分页？
所有回答(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
top 100 percent
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
如果是2005以上的版本，不需要这么麻烦做分页
http://www.cnblogs.com/anjou/archive/2007/10/17/926944.html
0
要是查询带where子句的话，肿么办。。。
一样的，我这里是联合连个表进行查询采用on，这联合查询的on 和单表查询的where是一样的
@听雨读诗: 下面是我写的另外一个查询，就是用到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
恭喜，解决了
