|
||||
if @pageindex <1 set @pageindex = 1
if @pageindex > @pagetotal
set @pageindex = @pagetotal
--检查输入页数,确保它在1到总页数之间
set @start = (@pageindex-1)*@pagesize+1
--游标第一次移动到的位置,比如我们需要第11到15条记录,那么这个参数的值是11
set @end= @pageindex*@pagesize
--游标第二次移动到的位置,如上,这个值应该是15
if @end > @total
set @end = @total--确保第二次移动不超过记录的总条数
set @i=@start
while(@i<=@end)
begin
fetch absolute @i from mycur into @id,@name
print @id+' '+@name
set @i=@i+1
end
end
else
begin
set @pagetotal = 0
select top 0 * from news
end
close mycur--关闭游标
deallocate mycur--释放游标
end
go
declare @pagetotal int
exec proc_cursor 5,7, @pagetotal output
--执行存储过程
print ' '
print '共'+convert(varchar(10),@pagetotal)+'页'
go
drop proc proc_cursor--删除存储过程
use master
go
drop database mydb--删除数据库
go
然后你在直接调用就OK啦么!
过程4:
create table book(
id int IDENTITY (1, 1) NOT NULL ,
category varchar(20) not null,
name varchar(20) not null,
author varchar(20) ,
publish varchar(20)
);
insert into book (category,name,author,publish) values
('物理','物理练习一','李力','大连');
insert into book (category,name,author,publish) values
('物理','物理练习二','王军','大连');
insert into book (category,name,author,publish) values
('物理','物理练习三','李力','大连');
insert into book (category,name,author,publish) values
('物理','物理练习四','王军','大连');
insert into book (category,name,author,publish) values
('英语','英语练习一','李力','大连');
insert into book (category,name,author,publish) values
('英语','英语练习二','王军','大连');
insert into book (category,name,author,publish) values
('英语','英语练习三','李力','大连');
insert into book (category,name,author,publish) values
('英语','英语练习四','王军','大连');
insert into book (category,name,author,publish) values
('数学','数学练习一','李力','大连');
insert into book (category,name,author,publish) values
('数学','数学练习二','李力','大连');
insert into book (category,name,author,publish) values
('数学','数学练习三','李力','大连');
insert into book (category,name,author,publish) values
('数学','数学练习四','李力','大连');
insert into book (category,name,author,publish) values
('数学','数学练习五','李力','大连');
insert into book (category,name,author,publish) values
('数学','数学练习六','李力','大连');
insert into book (category,name,author,publish) values
('数学','数学练习七','李力','大连');
SELECT TOP 2 * FROM book WHERE (ID NOT IN(SELECT TOP 4 idFROM bookORDER BY id))
ORDER BY ID;
分页公式:
select top 页大小 * from 表名
where (id not in(select top ((页码-1)*页大小) id from 表名 order by id)) order by id;
条件查询:
select top 页大小 * from table1 where category='数学'
and
(ID NOT IN (SELECT TOP ((页码-1)*页大小) id FROM table1 ORDER BY id)) ORDER BY ID;