34,594
社区成员
发帖
与我相关
我的任务
分享
alter proc pro_fy
@sql varchar(max),--需要查询的sql语句
@Pgcount int=10,--每页行数
@pgno int=1 --页码
as
begin
declare @str varchar(max)
set @str='declare @pgcount int,@pgno int,@start int,@end int,@sum int'
set @str=@str+CHAR(10)+'set @pgcount='+CAST(@Pgcount as varchar(10))+''
set @str=@str+CHAR(10)+'set @pgno='+CAST(@Pgno as varchar(10))+''
set @str=@str+CHAR(10)+'select @start=@pgcount*(@pgno-1)+1,@end=@pgcount*@pgno'
set @str=@str+CHAR(10)+'select * into #tb from ('+@sql+') a'
set @str=@str+CHAR(10)+'select @sum=ceiling(count(*)*1.0/@pgcount) from #tb'
set @str=@str+CHAR(10)+'select a.* from ('
set @str=@str+CHAR(10)+'select *,row_number() over(order by getdate()) as 行数,''第''+cast(@pgno as varchar(10))+''页'' as 页码,@sum as 总页数 from #tb) a'
set @str=@str+CHAR(10)+'where a.行数 between @start and @end'
exec(@str)
end
exec pro_fy 'select * from dbo.成绩信息表',7,1
1、
[code=SQL]select * from
(select *,row_num=ror_number() over (order by pk_id) from tb) b
where row_num between pagesize*(pageno-1) and pagesize*pageno
select top 100 * from
(
select top (5+1)*100 * from tableName order by PK asc
) order by PK desc