求一个统计数据的存储过程 或者SQL语句

[dongjielanyu]

有一个学生班级记录表
字段：机构编号、学生编号、学习状态（0在学；1毕业，2表示肄业） 加入班级时间 班级编号
我想求 2010年 每个机构中 所有参加某班级的总人数、在学人数、毕业人数、肄业人数 和毕业率

最好不要使用嵌套的SQL查询，这样的话在网页中使用这样的SQL语句 统计数据太慢了。 我编写的SQL语句如下：

		SELECT 
			A.Duty,
			A.UserId as OrganId,
			A.StudentNum,
			--F.CreditHour,
			IsNUll(Round(((F.CreditHour)*1.0/(A.StudentNum)*1.0),3),0) AS CreditHour,
			IsNUll(B.StudentOverNum,0) AS StudentOverNum,
			IsNull(C.StudentOnNum,0) AS StudentOnNum,
			IsNull(D.StudentPassNum,0) AS StudentPassNum,
			IsNull(Round(((B.StudentOverNum)*1.0/(A.StudentNum)*1.0),3)*100,0)  AS StudyOverRate,
			IsNull(E.StudyTime,0) AS StudyTime,
			--IsNull(E.AvgStudyTime,0) AS AvgStudyTime,
			IsNull(A.StudentNum-(B.StudentOverNum +C.StudentOnNum+D.StudentPassNum),0) AS DefereNum
		FROM
			--部门与总人数，平均学分
			(SELECT
				StudyClassStudentInfo.userID,
				USERINFO.ActualName As Duty,
				COUNT(StudyClassStudentInfo.StudentId) AS StudentNum,
				AVG(StudyClassStudentInfo.CreditHour) AS CreditHour
			FROM 
				StudyClassStudentInfo
			INNER JOIN
				USERINFO
			ON
				StudyClassStudentInfo.userid = userinfo.Id
			WHERE 
				StudyClassStudentInfo.StudyClassId = @p_StudyClassId
				AND
				Year(StudyClassStudentInfo.JoinTime) = @p_Year
--				And StudyClassStudentInfo.UserId in ( select Id from UserInfo where id=
--		@p_CurrOrganId or ParentId=@p_CurrOrganId)	
			Group By USERINFO.ActualName,StudyClassStudentInfo.userID) A
		LEFT JOIN
			(
				SELECT
					USERINFO.ActualName AS Duty,
					SUM(StudentCourseInfo.CreditHour*1.00) AS CreditHour
				FROM
					StudentCourseInfo
				INNER JOIN
					USERINFO
				ON 
					StudentCourseInfo.USERID = USERINFO.Id
				WHERE 
					StudentCourseInfo.CourseId in (SELECT CourseId FROM StudyClassCourseInfo WHERE StudyClassID=@p_StudyClassId)
--	And StudentCourseInfo.UserId in ( select Id from UserInfo where id=
--		@p_CurrOrganId or ParentId=@p_CurrOrganId)	
				GROUP BY USERINFO.ActualName
			) F
		ON
			F.Duty = A.Duty
		LEFT JOIN 
			--部门与结业人数
			(SELECT
				USERINFO.ActualName As Duty,
				COUNT(StudyClassStudentInfo.Status) AS StudentOverNum
			FROM 
				StudyClassStudentInfo
			INNER JOIN
				USERINFO
			ON
				StudyClassStudentInfo.USERID = USERINFO.Id
			WHERE 
				StudyClassStudentInfo.StudyClassId = @p_StudyClassId
					AND
				StudyClassStudentInfo.Status = 1
					AND
				Year(StudyClassStudentInfo.JoinTime) = @p_Year --And StudyClassStudentInfo.UserId in ( select Id from UserInfo where id=
		--@p_CurrOrganId or ParentId=@p_CurrOrganId)	
			Group By USERINFO.ActualName) B
			
		ON
			A.Duty = B.Duty
		LEFT JOIN
			--部门与在学人数
			(SELECT
				USERINFO.ActualName As Duty,
				COUNT(StudyClassStudentInfo.Status) AS StudentOnNum
			FROM 
				StudyClassStudentInfo
			INNER JOIN
				USERINFO
			ON
				StudyClassStudentInfo.USERID = USERINFO.Id
			WHERE 
				StudyClassStudentInfo.StudyClassId = @p_StudyClassId
					AND
				StudyClassStudentInfo.Status = 0
					AND
				Year(StudyClassStudentInfo.JoinTime) = @p_Year
-- And StudyClassStudentInfo.UserId in ( select Id from UserInfo where id=
--		@p_CurrOrganId or ParentId=@p_CurrOrganId)	
			Group By USERINFO.ActualName) C
		ON
			A.Duty = C.Duty 
		LEFT JOIN
			--部门与肄业人数
			(SELECT
				USERINFO.ACTUALNAME As Duty,
				COUNT(StudyClassStudentInfo.Status) AS StudentPassNum
			FROM 
				StudyClassStudentInfo
			INNER JOIN
				USERINFO
			ON
				StudyClassStudentInfo.USERID = USERINFO.Id
			WHERE 
				StudyClassStudentInfo.StudyClassId = @p_StudyClassId
					AND
				StudyClassStudentInfo.Status = 2
					AND
				Year(StudyClassStudentInfo.JoinTime) = @p_Year
-- And StudyClassStudentInfo.UserId in ( select Id from UserInfo where id=
--		@p_CurrOrganId or ParentId=@p_CurrOrganId)	
			Group By USERINFO.ActualName) D
		ON
			A.Duty = D.Duty		

		LEFT JOIN
			--部门与总学时，平均学时
			(SELECT 
				 USERINFO.ACTUALNAME As Duty,
				ISNULL(SUM(DateDiff(minute,StudentCourseLog.BeginTime,StudentCourseLog.EndTime)),0) AS StudyTime
			FROM 
				StudentCourseLog
			INNER JOIN 
				USERINFO
			ON
				StudentCourseLog.USERID = USERINFO.Id
			WHERE CourseId in
			(
				SELECT 
					CourseId 
				FROM 
					StudyClassCourseInfo 
				WHERE 
					StudyClassid = @p_StudyClassId
			)
			AND
				Year(StudentCourseLog.BeginTime) = @p_Year 
--			And StudentCourseLog.UserId in ( select Id from UserInfo where id=
--			@p_CurrOrganId or ParentId=@p_CurrOrganId)	
			GROUP By USERINFO.ActualName) E
		ON
			A.Duty = E.Duty	

这个SQL语句的执行速度太慢了 那位大大有没有更好的办法。。
没个机构可能会有下级机构 我想求的是 机构的学生的总数包括下级机构中的学生 。。。。。
请各位大神不吝赐教。。。。。。。

[-晴天]

咋找不到 2010 呢?

[dongjielanyu]

[Quote=引用 1 楼 qianjin036a 的回复:]

咋找不到 2010 呢?
[/Quote]
我的写的是一个存储过程 @p_year 可以表示2010

[-晴天]

用得着这么复杂么?
试试:

select 机构编号,班级编号,count(*)as 班级总人数 from 学生班级记录表 where 加入班级时间='2010' group by 机构编号,班级编号
select 机构编号,班级编号,count(*)as 在学人数 from 学生班级记录表 where 加入班级时间='2010' and 学习状态=0 group by 机构编号,班级编号
select 机构编号,班级编号,count(*)as 毕业人数 from 学生班级记录表 where 加入班级时间='2010' and 学习状态=1 group by 机构编号,班级编号
select 机构编号,班级编号,count(*)as 肄业人数 from 学生班级记录表 where 加入班级时间='2010' and 学习状态=2 group by 机构编号,班级编号
select 机构编号,班级编号,sum((case when 学习状态=1 then 1 else 0 end))/sum((case when 学习状态=1 or 学习状态=2 then 1 else 0 end)) as 毕业率 from 学生班级记录表 where 加入班级时间='2010' group by 机构编号,班级编号

[嘶皮尔伯嘞]

晴天 帮忙弄哈我的帖子啊
http://topic.csdn.net/u/20101214/10/84a2d1a3-1750-49f1-821b-2f3c5836db21.html?90869

[-晴天]

用存储过程,那就更简单啦!

CREATE PROCEDURE getmanythings
(
@year nvarchar(4),
@jgbh int,
@bjbh int,
@bjzrs int output,
@zxrs int output,
@byrs int output,
@yyrs int output,
@byr decimal(18,4) output
)
as
begin
select @bjzrs=count(*) from 学生班级记录表 where 加入班级时间=@year and 机构编号=@jgbh and 班级编号=bjbh
select @zxrs=count(*) from 学生班级记录表 where 加入班级时间=@year and 机构编号=@jgbh and 班级编号=bjbh and 学习状态=0
select @byrs=count(*) from 学生班级记录表 where 加入班级时间=@year and 机构编号=@jgbh and 班级编号=bjbh and 学习状态=1
select @yyrs=count(*) from 学生班级记录表 where 加入班级时间=@year and 机构编号=@jgbh and 班级编号=bjbh and 学习状态=2
set @byr=1.0000*@byrs/(@byrs+@yyrs)
end
go