基本的分页语句

基本的分页语句

31条数据 10条数据 4页
40条数据 10条件数 4页

–请查出表中前10条记录
–第一页
select top 10 * from StudentInfo where ID not in(select top 0 ID from StudentInfo)
–第二页
select top 10 * from StudentInfo where ID not in(select top 10 ID from StudentInfo)
–第三页
select top 10 * from StudentInfo where ID not in(select top 20 ID from StudentInfo)

–分页语句设计时，主要需要注意的问题就是ID不连续的问题
–分页语句一：
–pagesize表示一页显示多少条
–pageindex表示当前的页数
select top pagesize * from StudentInfo where ID not in(select top (pageindex-1)*pagesize ID from StudentInfo)
select top 10 * from student where id not in(select top 0 id from student)

–分页语句二：
–函数 row_number()排序函数
–where条件需要是原表存在的条件 而新查询出的列 不属于where的范畴
–row_number()函数是用来给数据库添加序列， over()括号中的参数是指：按照什么字段排序，并且可以指定升序还是降序
–子查询：将某一个查询结果当成一个表
select * from (select ROW_NUMBER() over(order by id asc) as rowNumber, * from StudentInfo)as t
where t.rowNumber between (pagesize-1)pageindex+1 and pageindexpagesize

select * from (select Row_Number() over (order by id asc)as rowNumber,*from StudentInfo)as t
where t.rowNumber between 10 and 20

select * from (select row_number()over (order by id asc)as rownumber,*from studentinfor)as t
where t.rownumber between 10 and 20
–注意：分页语句的写法有很多种，以上只是两种最基本的写法
– 分页语句好不好，要考虑的是性能问题，第二种写法的性能要高。
– 由于not in语法会破坏索引，导致数据检索效率降低，所以推荐使用第二种

select * from (select ROW_NUMBER()over (order by id asc)as row,* from StudentInfo)as t
where t.row between 10 and 20
select * from (select ROW_NUMBER()over(order by id asc)as rowNumber,* from student)as t
where t.rowNumber between 10 and 20
select top 10 * from student where ID not in (select top 10 id from student)
–0 10 20 30 40 50 60
–(n-1)*10

–1 10 1
–11 20 2
–21 30 3
–(n-1)10+1 n10

select * from StudentInfo where id between 1 and 10