优化前代码
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
优化后代码
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
而已看到优化后执行时间不用1秒
优化思路,第一个sql因为有三个其实查的都是同一条语句,但是因为子查询不能查三列,之前就是这样写的。
所以想着用左连接来优化,先取出一部分,再取出一部分然后连接。