我的这个sq执行将近1分钟，请求优化.....

最新推荐文章于 2020-11-21 04:56:52 发布

y527106719

最新推荐文章于 2020-11-21 04:56:52 发布

阅读量368

点赞数

文章标签：优化 join 作业 sql 2010

本文链接：https://blog.csdn.net/y527106719/article/details/6081566

版权

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
       (
---这个sql执行将近2万条数据
        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 = 517 AND CS.ClassID = 26 AND State = 1 AND Score > 0
     GROUP BY ER.StudentUserID