三种常用数据库（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：每页显示的记录数

 

参考资料

 

http://android.blog.51cto.com/268543/54310

 

http://bbs.youkuaiyun.com/topics/32008815