alter proc Pro_Fujet_Scores_Query1
@size int,
@index int,
@pagecount int output
as
begin
select top(@size) a.*, p.showImage,p.productID,p.Title,u.Email
from
(
select row_number() over(order by CreateOn desc) as num,*
from Scores where [level]>1
) as a
inner join OrderInfo o on a.orderNum=o.orderNum
inner join Products p on p.productID=o.productID
inner join UserInfo u on a.UserID=u.UserID
where a.num>@size*(@index-1)
order by a.CreateOn descselect @pagecount=((select count(*) from a)/@size)
end或者alter proc Pro_Fujet_Scores_Query1
@size int,
@index int,
@pagecount int output
as
begin
with a as
(
select row_number() over(order by CreateOn desc) as num,*
from Scores where level>1
)
select top(@size) a.*, p.showImage,p.productID,p.Title,u.Email
from a
inner join OrderInfo o on a.orderNum=o.orderNum
inner join Products p on p.productID=o.productID
inner join UserInfo u on a.UserID=u.UserID
where a.num>@size*(@index-1)
order by a.CreateOn descset @pagecount=((select count(*) from a)/@size)
end
都会出错
Msg 208, Level 16, State 1, Procedure Pro_Fujet_Scores_Query1, Line 2
对象名 'a' 无效。错在哪?
set @pagecount=((select count(*) from a)/@size)
第二个 with前面少个分号。with表达式定义后,只能使用一次。
alter proc Pro_Fujet_Scores_Query1
@size int,
@index int,
@pagecount int output
as
begin
select top(@size) a.*, p.showImage,p.productID,p.Title,u.Email
from
(
select row_number() over(order by CreateOn desc) as num,*
from Scores where [level]>1
) as a
inner join OrderInfo o on a.orderNum=o.orderNum
inner join Products p on p.productID=o.productID
inner join UserInfo u on a.UserID=u.UserID
where a.num>@size*(@index-1)
order by a.CreateOn descselect @pagecount=((select count(*) from
(select row_number() over(order by CreateOn desc) as num,*
from Scores where [level]>1)a)/@size)
end
这样就可以了。
分页,还有获取符合条件的总页数处处以获取?
差不多就那个row_Number 的最大值
alter proc Pro_Fujet_Scores_Query1
@size int,
@index int,
@pagecount int output
as
begin
with a as
(
select row_number() over(order by CreateOn desc) as num,*
from Scores where level>1
)
select top(@size) a.*, p.showImage,p.productID,p.Title,u.Email from a
inner join OrderInfo o on a.orderNum=o.orderNum
inner join Products p on p.productID=o.productID
inner join UserInfo u on a.UserID=u.UserID
where a.num>@size*(@index-1)
order by a.CreateOn descset @pagecount=((select @@ROWCOUNT)/@size)
end
alter proc Pro_Fujet_Scores_Query1
@size int,
@index int,
@pagecount int output
as
beginselect row_number() over(order by CreateOn desc) as num,* into #t
from Scores where level>1select top(@size) a.*, p.showImage,p.productID,p.Title,u.Email
from #t
inner join OrderInfo o on a.orderNum=o.orderNum
inner join Products p on p.productID=o.productID
inner join UserInfo u on a.UserID=u.UserID
where a.num>@size*(@index-1)
order by a.CreateOn descset @pagecount=((select count(*) from #t)/@size)
drop table #t
end
不行的,这样只能返回1 因为@@rowcount 的数是top(@size),而不是总行数