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

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