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

Question

有一个学生班级记录表
字段：机构编号、学生编号、学习状态（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语句的执行速度太慢了  那位大大有没有更好的办法。。 
 没个机构可能会有下级机构 我想求的是 机构的学生的总数包括下级机构中的学生 。。
请各位大神不吝赐教。

Answer 1

我的写的是一个存储过程 @p_year 可以表示2010

Answer 2

用得着这么复杂么?
试试:
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 机构编号,班级编号

Answer 3

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

Answer 4

用存储过程,那就更简单啦!
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