mysql oracle sqlserver分页,三种常用数据库（Oracle、MySQL、SQLServer）的分页之SQLServer分页...

环境

SQLServer 2008 R2

问题

SQLServer分页

解决

use test;

--创建测试表

create table test

(

id int primary key identity,

name varchar(20) not null

);

--插入数据

insert into test(name) values('test1');

insert into test(name) values('test2');

insert into test(name) values('test3');

insert into test(name) values('test4');

insert into test(name) values('test5');

insert into test(name) values('test6');

insert into test(name) values('test7');

insert into test(name) values('test8');

insert into test(name) values('test9');

insert into test(name) values('test10');

select * from test;

--执行分页

SELECT TOP 10 id,name

FROM test

WHERE id NOT IN

(SELECT TOP (10 * 0) id

FROM test

ORDER BY id)

ORDER BY ID

select id,name from test;

--select top pageSize * from tableName where id not in (select top pageSize * (pageNow - 1) id from tableName);

运行效果截图

小技巧

快速插入数据：

insert into test(name) select name from test;

总结语法

SELECT TOP页大小*

FROM TestTable

WHERE (ID NOT IN

(SELECT TOP页大小*页数id

FROM表

ORDER BY id))

ORDER BY ID

select top pageSize * from tableName where id not in (select top  pageSize * (pageNow - 1) id from tableName);

pageNow：当前第几页

pageSize：每页显示的记录数

参考资料