优化前代码
select *,ROW_NUMBER() OVER(order by WrongCount desc) as rowIdfrom(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因为有三个其实查的都是同一条语句，但是因为子查询不能查三列，之前就是这样写的。
所以想着用左连接来优化，先取出一部分，再取出一部分然后连接。