存储过程较慢请求优化

最新推荐文章于 2024-07-08 22:46:27 发布

y527106719

最新推荐文章于 2024-07-08 22:46:27 发布

阅读量465

点赞数 1

文章标签：存储 join 作业 2010 date object

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

版权

USE [BJnetwork929]
GO
/****** Object: StoredProcedure [db_accessadmin].[Up_Bap_SearchStudentStudyConditionNew]    Script Date: 03/10/2011 17:28:36 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:  付超
-- Create date: 2009-05-14
-- Description: 教师查看学生的学习情况
-- =============================================
ALTER PROCEDURE [db_accessadmin].[Up_Bap_SearchStudentStudyConditionNew]
@CourseID int,
@ClassID int,
@TeamID  int
AS
SELECT UserName     --用户名
  ,ActualName    --真实姓名
  ,ProvinceName   --省份名称
  ,Company    --单位
  ,CourseName    --课程名称
  ,OT.StudyTime   --在线学习时间
  ,OT.StudyCount   --在线学习次数
  ,ISNULL(HT.SubmitCouont,0) AS SubmitCouont --提交作业数
  --EDIT BY LZH START 2010.03.20
  --,ISNULL(WT.NOSubmitCouont,0) AS NOSubmitCouont --未提交作业数
  ,ISNULL(HT.NOSubmitCouont,0) AS NOSubmitCouont --未提交作业数
  --EDIT BY LZH END
  ,ISNULL(HS.ExerciseScore,0) + ISNULL(ER.ExaminationScore,0) + ISNULL(A.AdditionScore,0) AS GeneralAchievement --总成绩
  ,CU.CourseID   --课程ID
  ,TC.TeamID    --组ID
  ,SU.StudentUserID  --用户ID
FROM [db_owner].StudentUser SU --学员用户表
  INNER JOIN [db_owner].UserInfo UI ON SU.StudentUserID = UI.StudentUserID   --用户表
  LEFT JOIN [db_owner].SYS_ProvinceCode PC ON UI.Province = PC.ProvinceID    --省份表
  INNER JOIN [db_owner].StudyTeamInfoUser ST ON UI.StudentUserID = ST.StudentUserID --学习小组与用户关联表
  INNER JOIN [db_owner].StudyTeamInfoClass TC ON ST.TeamID = TC.TeamID    --学习小组与班级关联表
  --查询学生在线学习次数和在线学习时间
  INNER JOIN (SELECT StudentUserID
     ,CourseID
     ,COUNT(OnlineLearningRecorderID) AS StudyCount
     ,ISNULL(SUM(DateDiff(n,StartTime,EndTime)),0) AS StudyTime
     FROM [db_owner].OnlineLearningRecorder --在线学习记录
     WHERE CourseID = @CourseID
     GROUP BY StudentUserID,CourseID) AS OT ON SU.StudentUserID = OT.StudentUserID
  INNER JOIN [db_owner].Course CU ON OT.CourseID = CU.CourseID      --课程表'
  --查询学生提交作业数
  --edit by lzh start 2010.03.20
  --LEFT JOIN (SELECT TD.CourseID,StudentUserID,COUNT(ExerciseSubjectID) AS SubmitCouont
  LEFT JOIN (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 = @CourseID AND CL.ClassID = @ClassID
      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 ) AS HT ON CU.CourseID = HT.CourseID AND SU.StudentUserID = HT.StudentUserID
  --查询学生的作业总分数
  --edit by lzh 2010.3.24 start
  LEFT JOIN(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 = @CourseID AND CS.ClassID = @ClassID AND State = 1 AND Score > 0
     GROUP BY ER.StudentUserID) HS ON SU.StudentUserID = HS.StudentUserID
  --查询学生的试卷总分数
  --edit by lzh 2010.3.24 start
  LEFT JOIN(SELECT Convert(numeric(10,2),SUM(RE.Score*(ES.Rate/100.00))) AS ExaminationScore,RE.StudentUserID
     FROM [db_owner].CourseStatisticRate CS  --课程权重关联表
       INNER JOIN [db_owner].StatisticRateExamination ES ON CS.StatisticRateID = ES.StatisticRateID --权重评测关联表
       INNER JOIN [db_owner].ExamimationResult RE ON ES.ExaminationID = RE.ExaminationID   --答卷结果
       inner join
       (
        select MAX(ExaminationResultID) as ExaminationResultID
        from db_owner.ExamimationResult
        group by ExaminationID,StudentUserID
       ) as REM
       on RE.ExaminationResultID = REM.ExaminationResultID
     WHERE CS.CourseID = @CourseID AND CS.ClassID = @ClassID AND State = 1 AND RE.TeacherUserID IS NOT NULL
     GROUP BY RE.StudentUserID) ER ON SU.StudentUserID = ER.StudentUserID
  --查询学生的附加总分数
  LEFT JOIN(SELECT StudentUserID,Convert(numeric(10,2),SUM(AdditionScore)) AS AdditionScore
     FROM (
      SELECT StudentUserID,SUM(Score*(Rate/100.00)) AS AdditionScore
      FROM [db_owner].CourseStatisticRate CS   --课程权重关联表
        INNER JOIN [db_owner].StatisticRateAddition SA ON CS.StatisticRateID = SA.StatisticRateID --权重附加分关系表
        INNER JOIN [db_owner].AdditionScore A ON SA.AdditionID = A.AdditionID      --学生附加分表
      WHERE A.CourseID = @CourseID AND A.ClassID = @ClassID AND State = 1
      GROUP BY CS.StatisticRateID,A.AdditionID,Rate,StudentUserID) AS AC
     GROUP BY StudentUserID ) A ON SU.StudentUserID = A.StudentUserID
WHERE SU.UserType = 0 AND CU.CourseID = @CourseID AND TC.ClassID = @ClassID