• 多主键情况下的真分页存储过程


    前几天刚写的,对多个主键 多表 多个字段组合查询 多字段排序的情况下实现真分页的存储过程:
    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
  • 相关阅读:
    SpringMVC 返回 json 字符串中文乱码
    两个HTML页面之间传值
    NOIP2018 保卫王国
    Arch Linux使用cisco anyconnect
    CSP模拟赛 巨神兵
    计蒜客The Fake Fake Friends
    Manjaro安装,配置,美化指南
    [国家集训队] Crash 的文明世界
    ZJOI2011 营救皮卡丘
    CF1198E Rectangle Painting 2
  • 原文地址:https://www.cnblogs.com/songafeng/p/277499.html
Copyright © 2020-2023  润新知