SELECT TD.CourseID,TU.StudentUserID,COUNT(EU.ExerciseSubjectID) AS SubmitCouont
,COUNT(ES.ExerciseSubjectID)-COUNT(EU.ExerciseSubjectID) AS NOSubmitCouont
--edit by lzh end
FROM [db_owner].ExerciseSubject ES --作业表
INNER JOIN [db_owner].[CoursePlanUnit] CP ON ES.CoursePlanUnitID = CP.CoursePlanUnitID --课程计划单元表
INNER JOIN [db_owner].[TeachPlan] TP ON CP.TeachPlanID = TP.TeachPlanID --教学计划表
INNER JOIN [db_owner].[TeachDocment] TD ON TP.TeachDocumentID = TD.TeachDocmentID --教学文档表
INNER JOIN [db_owner].Class CL ON CL.CourseID = TD.CourseID AND CL.CourseID = 516 AND CL.ClassID = 535
INNER JOIN [db_owner].StudyTeamInfoClass TC ON CL.ClassID = TC.ClassID
INNER JOIN [db_owner].StudyTeamInfoUser TU ON TC.TeamID = TU.TeamID
--add by lzh start 2010.03.20
LEFT JOIN
(SELECT DISTINCT ExerciseSubjectID,StudentUserID FROM db_owner.ExerciseUpload) AS EU
ON ES.ExerciseSubjectID = EU.ExerciseSubjectID
AND TU.StudentUserID = EU.StudentUserID
GROUP BY TD.CourseID,TU.StudentUserID
SELECT Convert(numeric(10,2),SUM(ER.Score*(SE.Rate/100.00))) AS ExerciseScore,ER.StudentUserID
FROM [db_owner].CourseStatisticRate CS --课程权重关联表
INNER JOIN [db_owner].StatisticRateExercise SE ON CS.StatisticRateID = SE.StatisticRateID --权重作业关联表
INNER JOIN [db_owner].ExerciseResult ER ON SE.ExerciseSubjectID = ER.ExerciseContentID --作业结果
INNER join
(
select ExerciseResult.ExerciseContentID,ExerciseResult.StudentUserID
,MAX(ExerciseResult.ExerciseUploadID) as ExerciseUploadID
from db_owner.ExerciseResult
--add by liuzhh 2010.4.30 start
where Teacher != ''
--add end
group by ExerciseResult.ExerciseContentID,ExerciseResult.StudentUserID
) as ERM
on ER.ExerciseContentID = ERM.ExerciseContentID
and ER.StudentUserID = ERM.StudentUserID
and ER.ExerciseUploadID = ERM.ExerciseUploadID
WHERE CS.CourseID = 516 AND CS.ClassID = 535 AND State = 1 AND Score > 0
GROUP BY ER.StudentUserID
存储过程执行很慢,请求优化
最新推荐文章于 2024-06-26 14:53:39 发布