• 分页存储过程


    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   
    

      

  • 相关阅读:
    oracle 巡检脚本(自动化) 规格严格
    应用版日常linux系统巡检shell脚本 规格严格
    linux系统巡检脚本 规格严格
    超级详细RPM 规格严格
    项目管理纪实一:需求调研日志
    其实你不懂程序员
    Silverlight同步(Synchronous)调用WCF服务
    GeoServer地图开发解决方案(一):环境搭建篇
    新年新起点荣获2011年度Silverlight方向Microsoft® MVP奖
    工作中发现 VC 通过 Flex 访问 FusionChart for FLEX 当VC端的驱动数据串过长时,会出现 No Data to Display错误
  • 原文地址:https://www.cnblogs.com/muxueyuan/p/5025521.html
Copyright © 2020-2023  润新知