• 分页存储过程


    USE [DB_JP_BaseInfo]
    GO
    /****** Object:  StoredProcedure [dbo].[sp_HeGeIndexReport]    Script Date: 12/07/2015 10:28:21 ******/
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    ------------------------------------
    --根据时间段查询报名点的合格率情况--
    ------------------------------------
    ALTER PROCEDURE [dbo].[sp_HeGeIndexReport]
    	
    	@pageSize int=1,				--分页数量
    	@pageIndex int=1,               --当前页
    	@orderby varchar(50)='A.ID',          --排序字段
    	--@sortType int='asc', 
    	@StartTime datetime,                 --排序类型
    	@EndTime datetime,
    	--@where varchar(max) --查询条件
    AS
     BEGIN
     Declare @beginRow int, @endRow int, @sql varchar(max) 
     Set @beginRow = @pageSize * (@pageIndex - 1) + 1
     Set @endRow = @pageSize * @pageIndex
    Declare @mainSql varchar(max), @getCountSql varchar(max)
    set @mainSql='Select * ,ROW_NUMBER() OVER (ORDER BY '+@orderby+') As row From 
    (
    	SELECT aa.*,
           (CASE
                WHEN aa.KeMuYiBaoMingCount=0 THEN 0
                ELSE aa.KeMuYiHeGeCount/aa.KeMuYiBaoMingCount
            END) AS KeMuYiHeGeIndex,
           (CASE
                WHEN aa.KeMuErBaoMingCount=0 THEN 0
                ELSE aa.KeMuErHeGeCount/aa.KeMuErBaoMingCount
            END) AS KeMuYiErGeIndex,
           (CASE
                WHEN aa.KeMuSanBaoMingCount=0 THEN 0
                ELSE aa.KeMuSanHeGeCount/aa.KeMuSanBaoMingCount
            END) AS KeMuSanHeGeIndex,
           (CASE
                WHEN aa.KeMuSiBaoMingCount=0 THEN 0
                ELSE aa.KeMuSiHeGeCount/aa.KeMuSiBaoMingCount
            END) AS KeMuSiHeGeIndex
    FROM
      ( SELECT reg_tem.RegSiteNo,
               reg_tem.RegSiteName,
    
         (SELECT count(*)
          FROM CheckResults
          WHERE CheckType=''科目一''
            AND @StartTime<CheckTime
            AND CheckTime<@EndTime
            AND StuId IN
              (SELECT StuId
               FROM student
               WHERE RegSiteId=reg_tem.id
                 AND isDelete=0)) AS KeMuYiBaoMingCount,
    
         (SELECT count(*)
          FROM CheckResults
          WHERE CheckType=''科目一''
            AND @StartTime<CheckTime
            AND CheckTime<@EndTime
            AND CheckResult=1
            AND StuId IN
              (SELECT StuId
               FROM student
               WHERE RegSiteId=reg_tem.id
                 AND isDelete=0)) AS KeMuYiHeGeCount,
    
         (SELECT count(*)
          FROM CheckResults
          WHERE CheckType=''科目一''
            AND @StartTime<CheckTime
            AND CheckTime<@EndTime
            AND CheckResult=2
            AND StuId IN
              (SELECT StuId
               FROM student
               WHERE RegSiteId=reg_tem.id
                 AND isDelete=0)) AS KeMuYiQueKaoCount,
    
         (SELECT count(*)
          FROM CheckResults
          WHERE CheckType=''科目二''
            AND @StartTime<CheckTime
            AND CheckTime<@EndTime
            AND StuId IN
              (SELECT StuId
               FROM student
               WHERE RegSiteId=reg_tem.id
                 AND isDelete=0)) AS KeMuErBaoMingCount,
    
         (SELECT count(*)
          FROM CheckResults
          WHERE CheckType=''科目二''
            AND @StartTime<CheckTime
            AND CheckTime<@EndTime
            AND CheckResult=1
            AND StuId IN
              (SELECT StuId
               FROM student
               WHERE RegSiteId=reg_tem.id
                 AND isDelete=0)) AS KeMuErHeGeCount,
    
         (SELECT count(*)
          FROM CheckResults
          WHERE CheckType=''科目二''
            AND @StartTime<CheckTime
            AND CheckTime<@EndTime
            AND CheckResult=2
            AND StuId IN
              (SELECT StuId
               FROM student
               WHERE RegSiteId=reg_tem.id
                 AND isDelete=0)) AS KeMuErQueKaoCount,
    
         (SELECT count(*)
          FROM CheckResults
          WHERE CheckType=''科目三''
            AND @StartTime<CheckTime
            AND CheckTime<@EndTime
            AND StuId IN
              (SELECT StuId
               FROM student
               WHERE RegSiteId=reg_tem.id
                 AND isDelete=0)) AS KeMuSanBaoMingCount,
    
         (SELECT count(*)
          FROM CheckResults
          WHERE CheckType=''科目三''
            AND @StartTime<CheckTime
            AND CheckTime<@EndTime
            AND CheckResult=1
            AND StuId IN
              (SELECT StuId
               FROM student
               WHERE RegSiteId=reg_tem.id
                 AND isDelete=0)) AS KeMuSanHeGeCount,
    
         (SELECT count(*)
          FROM CheckResults
          WHERE CheckType=''科目三''
            AND @StartTime<CheckTime
            AND CheckTime<@EndTime
            AND CheckResult=2
            AND StuId IN
              (SELECT StuId
               FROM student
               WHERE RegSiteId=reg_tem.id
                 AND isDelete=0)) AS KeMuSanQueKaoCount,
    
         (SELECT count(*)
          FROM CheckResults
          WHERE CheckType=''科目四''
            AND @StartTime<CheckTime
            AND CheckTime<@EndTime
            AND StuId IN
              (SELECT StuId
               FROM student
               WHERE RegSiteId=reg_tem.id
                 AND isDelete=0)) AS KeMuSiBaoMingCount,
    
         (SELECT count(*)
          FROM CheckResults
          WHERE CheckType=''科目四''
            AND @StartTime<CheckTime
            AND CheckTime<@EndTime
            AND CheckResult=1
            AND StuId IN
              (SELECT StuId
               FROM student
               WHERE RegSiteId=reg_tem.id
                 AND isDelete=0)) AS KeMuSiHeGeCount,
    
         (SELECT count(*)
          FROM CheckResults
          WHERE CheckType=''科目四''
            AND @StartTime<CheckTime
            AND CheckTime<@EndTime
            AND CheckResult=2
            AND StuId IN
              (SELECT StuId
               FROM student
               WHERE RegSiteId=reg_tem.id
                 AND isDelete=0)) AS KeMuSiQueKaoCount
       FROM regsite reg_tem
       WHERE reg_tem.Isdelete=0 ) aa
    ) A '+@where
    			
    set @sql = 'Select * From 
    ('+@mainSql+'
    ) T
    Where T.row between '+Convert(nvarchar(9),@beginRow)+' and '+Convert(nvarchar(9),@endRow)
    
    Set @getCountSql = 'Select Count(*) From ('+@mainSql+') Tb'--根据条件获取总数量
    
    EXECUTE(@sql)
    EXECUTE(@getCountSql)
     END   
    

      

  • 相关阅读:
    为什么叫Windows 7 ?
    关于多线程生命周期原理
    关于浏览器创建XMLHttpRequest对象
    关于ajax异步加载XML例子
    关于多线程简单原理
    指针和指针的引用
    linux学习点滴
    GCC,GDB,Makefile
    妙语集锦
    uc/os内存管理的理解
  • 原文地址:https://www.cnblogs.com/muxueyuan/p/5025521.html
Copyright © 2020-2023  润新知