-对多个主键 多表 多个字段组合查询 多字段排序的情况下实现真分页的存储过程

此存储过程用于根据学校ID、教师姓名及推送状态等条件搜索教师信息,并支持分页查询。它通过连接Teacher、School和TeacherRecommand表来获取所需数据。

摘要生成于 C知道 ,由 DeepSeek-R1 满血版支持, 前往体验 >

CREATE procedure TEACHERS_SEARCH @schoolid as int=0, @teacher_name as nvarchar(50)='', @push_status as tinyint=0, @count as tinyint=0, --真分页统计 @pageSize as int=10, --当前页需要显示的记录数 @topSize as int=10 --包括该页前面的记录总数 as --@status 0:全部;1:在课件区;2:在blog区;3:既在课件区又在blog区; declare @str nvarchar(2000) declare @str_search nvarchar(1000) set @str_search='1=1' if(@schoolid<>0) set @str_search=@str_search+' and teacher.schoolid='+str(@schoolid) if(@teacher_name<>'') set @str_search=@str_search+' and teacher.name like '''+@teacher_name +'%''' if(@push_status=1) set @str_search=@str_search+' and TeacherRecommand.status in (1,3)' if(@push_status=2) set @str_search=@str_search+' and TeacherRecommand.status in (2,3)' if(@push_status=3) set @str_search=@str_search+' and TeacherRecommand.status in (1,2)' if(@count=0) set @str=' select * from (SELECT top '+ str(@pageSize) +' * from ( select top '+ str(@topSize) +' Teacher.schoolid,Teacher.teacherid,teacher.name as teacher_name,school.name as school_name,teacher.email,teacher.telephone, TeacherRecommand.status,School.rainbowsite as rainbowsite FROM Teacher INNER JOIN School ON Teacher.schoolid = School.schoolid LEFT OUTER JOIN TeacherRecommand ON Teacher.schoolid = TeacherRecommand.schoolid AND Teacher.teacherid = TeacherRecommand.teacherid WHERE '+@str_search +' order by teacher.schoolid desc,teacher.teacherid desc) a order by schoolid asc,teacherid asc) b order by schoolid desc,teacherid desc' else set @str=' SELECT count(*) FROM Teacher INNER JOIN School ON Teacher.schoolid = School.schoolid LEFT OUTER JOIN TeacherRecommand ON Teacher.schoolid = TeacherRecommand.schoolid AND Teacher.teacherid = TeacherRecommand.teacherid WHERE '+@str_search --print @str exec (@str) GO

评论 1
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值