1
declare @PageSize int--每页的大小(行数)
2
declare @PageCurrent int --要显示的页 从1开始
3
set @PageSize=10
4
set @PageCurrent=11
5
create table test(
6
[id][int]IDENTITY (1, 1)not null,
7
[name][varchar](10)null,
8
[age][int]null
9
) ON [PRIMARY]
10
go
11
select * from test
12
declare @n int
13
set @n=1
14
while(@n<=100000)
15
begin
16
insert into test values('n'+cast(@n as varchar),@n)
17
set @n=@n+1
18
end
19
select * from test
20![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
21
--method1:use not in
22
declare @nn int
23
set @nn = @PageSize*(@PageCurrent-1)
24
declare @str varchar(1000)
25
set @str='select top '+cast(@PageSize as varchar)+'* from test where id not in(select top '+cast(@nn as varchar)+' id from test)'
26
--method2:use >id
27
set @nn = @PageSize*(@PageCurrent-1)
28
declare @str varchar(1000)
29
set @str='select top '+cast(@PageSize as varchar)+'* from test where id>((select max(id) from (select top '+cast(@nn as varchar)+' id from test ) as m))'
30
-------------------------------------------------------
31
/*如果表没有标识列,则加个标识列*/
32
-------------------------------------------------------
33
--method1
34
alter table test
35
add id int identity(1,1)
36
--中间操作和上面一样
37![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
38
--update test set id=''
39
Alter table test Drop column id
40![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
41
--method2
42
select * into #temp from test
43
alter table #temp
44
add tel varchar(20) null
45![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
46![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
2
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
3
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
4
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
5
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
6
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
7
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
8
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
9
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
10
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
11
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
12
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
13
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
14
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
15
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
16
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
17
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
18
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
19
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
20
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
21
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
22
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
23
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
24
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
25
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
26
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
27
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
28
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
29
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
30
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
31
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedBlockStart.gif)
32
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
33
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
34
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
35
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
36
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
37
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
38
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
39
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
40
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
41
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
42
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
43
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
44
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
45
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
46
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)