一段sql的优化

时间:2022-01-31 22:22:41

优化前代码

select *
,ROW_NUMBER() OVER(order by WrongCount desc) as rowId
from(select Quba_IDint,Quba_Number
,
(select top 1 Sqre_AddDateTime
from tbStudentStudyQuestionRecords where Sqre_QubaId=Quba_IDint and Sqre_StudentId=200
and sqre_AnswerJudge='wrong' order by Sqre_AddDateTime desc) as Sqre_AddDateTime,
(select top 1 Sqre_StudyFromType
from tbStudentStudyQuestionRecords where Sqre_QubaId=Quba_IDint and Sqre_StudentId=200
and sqre_AnswerJudge='wrong' order by Sqre_AddDateTime desc) as Sqre_StudyFromType,
COUNT(Quba_IDint) as WrongCount
,COUNT(distinct Expo_KnowPointIDint) as KnpoCount
,
(select top 1 Sqre_MainId
from tbStudentStudyQuestionRecords where Sqre_QubaId=Quba_IDint and Sqre_StudentId=200 and sqre_AnswerJudge='wrong'
order by Sqre_AddDateTime desc) as Sqre_MainId
from tbStudentStudyQuestionRecords
left join tbQuestionBank on Sqre_QubaId=Quba_IDint
left join tbQuestionType on QuTy_Id=Quba_Type
left join tbExamKnowPoint on Expo_ExamIDint=Quba_IDint
where Sqre_StudentId=200 and Quba_SubjectId=15 and Sqre_AnswerJudge='wrong' and QuTy_Name<>'综合题'
group by Quba_IDint,Quba_Number)as t order by quba_idint

  一段sql的优化

优化后代码

select t.*,Sqre_AddDateTime,Sqre_StudyFromType,Sqre_MainId,Sqre_QubaId,
ROW_NUMBER() OVER(order by WrongCount desc) as rowId
from (select Quba_IDint,Quba_Number,QuTy_Name,
COUNT(Quba_IDint) as WrongCount
,COUNT(distinct Expo_KnowPointIDint) as KnpoCount,max(Sqre_Id) as lastId
from tbStudentStudyQuestionRecords
left join tbQuestionBank on Sqre_QubaId=Quba_IDint
left join tbQuestionType on QuTy_Id=Quba_Type
left join tbExamKnowPoint on Expo_ExamIDint=Quba_IDint
where Sqre_StudentId=200 and sqre_AnswerJudge='wrong' and Quba_SubjectId=15 and QuTy_Name<>'综合题'
group by Quba_IDint,Quba_Number,QuTy_Name) as t
left join tbStudentStudyQuestionRecords on t.lastId=Sqre_Id

一段sql的优化 

而已看到优化后执行时间不用1秒 

优化思路,第一个sql因为有三个其实查的都是同一条语句,但是因为子查询不能查三列,之前就是这样写的。

所以想着用左连接来优化,先取出一部分,再取出一部分然后连接。