数据库分页查询语句（sql、access）

--sql
select top 10 *,ROW_NUMBER() over (order by id asc,name asc) as rows 
from student 
where name like '%4%'

select top 10 * from (select *,ROW_NUMBER() over (order by id asc, name asc) as rows 
from student 
where name like '%4%') as tb2 
where rows>10

select top 10 *,ROW_NUMBER() over (order by id desc,name desc) as rows 
from student 
where name like '%4%'

select top 10 * from (select *,ROW_NUMBER() over (order by id desc,name desc) as rows 
from student 
where name like '%4%') as tb2 
where rows>10

--sql、access
select top 10 * from student where name like '%4%' order by id asc,name asc

select top 10 * from student where name like '%4%' 
and id not in (select top 10 id from Student where name like '%4%' order by id asc,name asc) 
order by id asc,name asc

select top 10 * from Student where name like '%4%' order by id desc,name desc

select top 10 * from student where name like '%4%' 
and id not in (select top 10 id from Student where name like '%4%' order by id desc,name desc) 
order by id desc,name desc

--sql分页存储过程
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paging]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[paging]
GO
CREATE PROCEDURE [paging]
@tableName nvarchar(32),--表名
@pageSize int=10,--每页显示数据
@pageIndex int=1,--当前页数
@strFields nvarchar(max),--字段
@strJoin nvarchar(max),--联接
@strWhere nvarchar(max),--筛选
@strOrderBy nvarchar(max)--排序
AS
 declare @strSQL   nvarchar(max) 
 declare @indexStart int
 set @indexstart=@pageIndex*@pageSize      
 set @strSQL=' select top '+convert(nvarchar(10),@PageSize)+' *from (select '+@strFields+' over ('+@strOrderBy+') as rowNum 
from '+@tableName+' '+@strJoin+' '+@strWhere+') as tb where rowNum>'+convert(nvarchar(10),@indexstart)
 print @strSQL
 exec (@strSQL)
GO
--执行存储过程
exec paging   
'dbo.studentDemo',  
5,  
0,  
'dbo.studentDemo.StudentId,dbo.studentDemo.StudentName,dbo.classDemo.ClassName,row_number()',  
'inner join dbo.classDemo on dbo.studentDemo.ClassId=dbo.classDemo.ClassId',  
'where 1=1 and dbo.studentDemo.StudentName like ''%S%''',  
'order by dbo.studentDemo.StudentId asc' 

--运行生成sql:
 select top 5 *from (
 select 
 dbo.studentDemo.StudentId
 ,dbo.studentDemo.StudentName
 ,dbo.classDemo.ClassName
 ,row_number() over (order by dbo.studentDemo.StudentId asc) as rowNum 
 from dbo.studentDemo 
 inner join dbo.classDemo 
 on dbo.studentDemo.ClassId=dbo.classDemo.ClassId 
 where 1=1 and dbo.studentDemo.StudentName like '%S%'
) as tb where rowNum>5