--sql server 分页语句
--首先创建一个测试用的表,并且插入一些测试数据
if
exists (select * from sysobjects where name='test')
drop table test
go
create
table test
(
id int primary key identity(1,1),
name
varchar(20)
)
go
insert
into test values('aaa')
insert
into test values('bbb')
insert
into test values('vvv')
insert
into test values('fff')
insert
into test values('eee')
insert
into test values('ggg')
insert
into test values('ddd')
insert
into test values('sss')
insert
into test values('ttt')
insert
into test values('555')
insert
into test values('uyuu')
insert
into test values('vvjhgjv')
insert
into test values('ffghfgdf')
insert
into test values('fsdfsdf')
insert
into test values('rewrew')
insert
into test values('fsdfsd')
insert
into test values('gfdgf')
insert
into test values('ttgsdsdbt')
insert
into test values('fdsfdsaf')
insert
into test values('ghfdsfs')
insert
into test values('vvvhgfhg')
insert
into test values('fdsfsgffff')
insert
into test values('eeehgfhjgf')
insert
into test values('ggghgjgf')
insert
into test values('dddfdsafdd')
insert
into test values('sssfdsfds')
insert
into test values('jhkjkjttt')
select
* from test
执行结果:
id name
1 aaa
2 bbb
3 vvv
4 fff
5 eee
6 ggg
7 ddd
8 sss
9 ttt
10 555
11 uyuu
12 vvjhgjv
13 ffghfgdf
14 fsdfsdf
15 rewrew
16 fsdfsd
17 gfdgf
18 ttgsdsdbt
19 fdsfdsaf
20 ghfdsfs
21 vvvhgfhg
22 fdsfsgffff
23 eeehgfhjgf
24 ggghgjgf
25 dddfdsafdd
26 sssfdsfds
27 jhkjkjttt
--
创建一个简单的存储过程来实现分页:
if
exists (select * from sysobjects where name='proc_page')
drop proc proc_page
go
create
proc proc_page
@num int=10,--
此存储过程接收两个参数,第一个参数为每页显示多少条记录
,
默认为
10
条。
@page int=1--
第二个参数为显示第几页,默认为第
1
页。
as
if((select count(*) from test)-(@page-1)*(@num)<=0)--
判断如果超出总行数的范围则提示用户
print '
已超出最大页数!请缩小要显示页号或者减少每页要显示的行数!
'
else
select top (@num) * from (select top ((select count(*) from test)-((@page-1)*(@num))) * from test order by id desc) as a order by id asc
go
调用不带参数的,默认显示第一页,每页
10
条。
exec
proc_page
go
执行结果:
id name
1 aaa
2 bbb
3 vvv
4 fff
5 eee
6 ggg
7 ddd
8 sss
9 ttt
10 555
带一个参数的,每页的行数为给定的默认值
10
条,显示第三页。
exec
proc_page @page=3
go
执行结果:
id name
21 vvvhgfhg
22 fdsfsgffff
23 eeehgfhjgf
24 ggghgjgf
25 dddfdsafdd
26 sssfdsfds
27 jhkjkjttt
测试超出范围的,一共
27
条,这里想要显示的是
31
到
40
条记录,所以提示错误了。
exec
proc_page @page=4
go
执行结果:
已超出最大页数!请缩小要显示页号或者减少每页要显示的行数!
带两个参数的,每页
5
条,显示第
3
页
exec
proc_page 5,3
go
执行结果:
id name
11 uyuu
12 vvjhgjv
13 ffghfgdf
14 fsdfsdf
15 rewrew