 |
|
 |
||
|
|
|
||
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;
