--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
数据库分页查询语句(sql、access)
最新推荐文章于 2022-10-27 16:57:59 发布