分页存储过程

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