最近项目报表开发中写的储存过程-成绩纵向对比

CREATE PROCEDURE select_MarkContrastByTime
@currentSemester int,
@currentAchievement varchar(50),
@semester varchar(50),
@grade varchar(50),
@tSubject_id int,
@achievementName varchar(500)
AS
BEGIN
SET NOCOUNT ON
---------------参数传递示例-------------------------------------------------------------------
--declare @semester varchar(50),@grade varchar(50),@tSubject_id int,@achievementName varchar(500),@rate decimal
--set @semester='35,36'
--set @grade='18,18'
--set @achievementName='期中考试,期中考试'
--set @tSubject_id=3
declare @strWhere varchar(8000)--定义条件表达式变量
set @strWhere=''
declare @strAsc varchar(8000)--定义级部排名变量
set @strAsc=''
declare @next int  --定义循环步长变量
set @next=1  
-------------------------以下为循环--------------------------------------------
while @next<=dbo.Get_StrArrayLength(@semester,',')  
begin
--------------------------------------定义变量@sqlShare，并赋值-----------------------------------------------
declare @sqlShare varchar(8000)
set @sqlShare='select class_id,semester_id,achievementName as 哪次考试,学年+学期+年级+achievementName as achievementName ,班级,5 as typeId,
''级部排名'' as 对比项目,cast(avg(scoreNumber) as decimal(10,2)) as 对比值 from dbo.aAchievementMarkWithTeacherView where (achievementName='''
+dbo.Get_StrArrayStrOfIndex(@achievementName,',',@next)
+''' and semester_id='+ dbo.Get_StrArrayStrOfIndex(@semester,',',@next)
+' and grade_id='+ dbo.Get_StrArrayStrOfIndex(@grade,',',@next)
+' and tSubject_id='+cast(@tSubject_id as varchar(50))+') 
group by class_id,semester_id,学年,学期,年级,achievementName,班级' 
--------------------------条件判断--------------------------------------------------------------
if @next=dbo.Get_StrArrayLength(@semester,',')--如果为最后一条记录
begin
---------------------先给@strWhere赋值----------------------------------
set @strWhere= @strWhere+'achievementName='''
+dbo.Get_StrArrayStrOfIndex(@achievementName,',',@next)

+''' and semester_id='+ dbo.Get_StrArrayStrOfIndex(@semester,',',@next)
+' and grade_id='+ dbo.Get_StrArrayStrOfIndex(@grade,',',@next)
+' and tSubject_id='+cast(@tSubject_id as varchar(50))+') '
---------------------再给@strAsc赋值----------------------------------
set @strAsc=@strAsc+' select class_id,semester_id,哪次考试,achievementName,班级,typeId,对比项目,对比值=cast((select count(对比值) from ('+@sqlShare+')as newTable1  where newTable1.对比值>= newTable.对比值) as varchar(50)) from ('+@sqlShare+')as newTable '
end
else
begin
-------------------先给@strWhere赋值----------------------------------
set @strWhere= @strWhere+'achievementName='''+dbo.Get_StrArrayStrOfIndex(@achievementName,',',@next)
+''' and semester_id='+ dbo.Get_StrArrayStrOfIndex(@semester,',',@next)
+' and grade_id='+ dbo.Get_StrArrayStrOfIndex(@grade,',',@next)
+' and tSubject_id='+cast(@tSubject_id as varchar(50))+') or ('
-------------------再给@strAsc赋值----------------------------------
 set @strAsc=@strAsc+' select class_id,semester_id,哪次考试,achievementName,班级,typeId,对比项目,对比值=cast((select count(对比值) from ('+@sqlShare+')as newTable1  where newTable1.对比值>= newTable.对比值) as varchar(50)) from ('+@sqlShare+')as newTable  union'
end  
set @next=@next+1
end 
-------------结束循环-----------------------------------------------------------------------
declare @sqlstr1 varchar(8000)--定义sql语句拼接变量
set @sqlstr1=' select class_id,semester_id,achievementName as 哪次考试,学年+学期+年级+achievementName as achievementName ,班级, 1 as typeId,
''班级'' as 对比项目,
班级 as 对比值
from dbo.aAchievementMarkWithTeacherView where ('
declare @sqlstr2 varchar(8000)--定义sql语句拼接变量
set @sqlstr2='group by class_id,semester_id,学年,学期,年级,achievementName,班级,教者 '
+' union select class_id,semester_id,achievementName as 哪次考试,学年+学期+年级+achievementName as achievementName ,班级,2 as typeId,
''平均分'' as 对比项目,
cast(cast(avg(scoreNumber) as decimal(10,2)) as varchar(50)) as 对比值
from dbo.aAchievementMarkWithTeacherView where ('
declare @sqlstr3 varchar(8000)--定义sql语句拼接变量
set @sqlstr3='group by class_id,semester_id,学年,学期,年级,achievementName,班级'
+' union select class_id,semester_id,achievementName as 哪次考试,学年+学期+年级+achievementName as achievementName ,班级,3 as typeId,
''及格率(%)'' as 对比项目,
case 
when sum(case  when scoreNumber>=60 then 1 else 0 end )=0 then ''0''
when count(s_id)=0 then ''0'' 
else cast(cast(100*sum(cast(case  when scoreNumber>=60 then 1 else 0 end  as decimal))/count(s_id) as decimal(10,2)) as varchar(50))
end  as 对比值
from dbo.aAchievementMarkWithTeacherView where ('
declare @sqlstr4 varchar(8000)--定义sql语句拼接变量
set @sqlstr4='group by class_id,semester_id,学年,学期,年级,achievementName,班级'
+' union select class_id,semester_id,achievementName as 哪次考试,学年+学期+年级+achievementName as achievementName ,班级,4 as typeId,
''教者'' as 对比项目,
教者 as 对比值
from dbo.aAchievementMarkWithTeacherView where ('
declare @sqlstr5 varchar(8000)--定义sql语句拼接变量
set @sqlstr5=+'group by class_id,semester_id,学年,学期,年级,achievementName,班级,教者 union'

--set @sqlstr=@sqlstr+@strAsc
declare @sqlHead varchar(8000)
set @sqlHead='select class_id,achievementName,班级, typeId,case when semester_id='+cast(@currentSemester as varchar(50))+'  and  哪次考试='''+@currentAchievement+''' then ''当前情况'' else ''对比情况'' end  as 对比对象,对比项目,对比值 from  ('
declare @sqlEnd varchar(50)
set @sqlEnd=') as LastTable order by 对比对象 '
exec(@sqlHead+@sqlstr1+@strWhere+@sqlstr2+@strWhere+@sqlstr3+@strWhere+@sqlstr4+@strWhere+@sqlstr5+@strAsc+@sqlEnd)
--执行语句
END
GO