坑爹的sql,好不容易写成这样,谁知道,必须得有userid参照值,否则就报错,求解救
select (select RowNum from (select row_number()over(order by Achievement_Content desc) as RowNum,* from FM_Achievement table1 where table1.Achievement_ExamId=FM_Achievement.Achievement_ExamId and table1.Achievement_SubjectId=FM_Achievement.Achievement_SubjectId and table1.Sid=FM_Achievement.Sid ) table2 where FM_Achievement.Achievement_UserId=table2.Achievement_UserId) as gradenum, (select RowNum from (select row_number()over(order by Achievement_Content desc) as RowNum,* from FM_Achievement table1 where table1.Achievement_ExamId=FM_Achievement.Achievement_ExamId and table1.Achievement_SubjectId=FM_Achievement.Achievement_SubjectId and table1.Sid=FM_Achievement.Sid and table1.Achievement_Class=FM_Achievement.Achievement_Class ) table2 where FM_Achievement.Achievement_UserId=table2.Achievement_UserId) as classnum, (select MAX(Achievement_Content) from (select row_number()over(order by Achievement_Content desc) as RowNum,* from FM_Achievement table1 where table1.Achievement_ExamId=FM_Achievement.Achievement_ExamId and table1.Achievement_SubjectId=FM_Achievement.Achievement_SubjectId and table1.Sid=FM_Achievement.Sid ) table2 ) as gradehig, (select MAX(Achievement_Content) from (select row_number()over(order by Achievement_Content desc) as RowNum,* from FM_Achievement table1 where table1.Achievement_ExamId=FM_Achievement.Achievement_ExamId and table1.Achievement_SubjectId=FM_Achievement.Achievement_SubjectId and table1.Sid=FM_Achievement.Sid and table1.Achievement_Class=FM_Achievement.Achievement_Class ) table2) as classhig, (select avg(Achievement_Content) from (select row_number()over(order by Achievement_Content desc) as RowNum,* from FM_Achievement table1 where table1.Achievement_ExamId=FM_Achievement.Achievement_ExamId and table1.Achievement_SubjectId=FM_Achievement.Achievement_SubjectId and table1.Sid=FM_Achievement.Sid ) table2) as gradeavg, (select avg(Achievement_Content) from (select row_number()over(order by Achievement_Content desc) as RowNum,* from FM_Achievement table1 where table1.Achievement_ExamId=FM_Achievement.Achievement_ExamId and table1.Achievement_SubjectId=FM_Achievement.Achievement_SubjectId and table1.Sid=FM_Achievement.Sid and table1.Achievement_Class=FM_Achievement.Achievement_Class ) table2) as classavg, (select (Grade_Name+Class_Name) as name1 from dbo.FM_GradeClass where FM_GradeClass.Grade_Id=FM_Achievement.Achievement_Class) as GradeClassName, (select Exam_Name from dbo.FM_Exam where FM_Exam.Exam_Id=FM_Achievement.Achievement_ExamId) as ExamName, (select ExamTotalNum from FM_Exam where FM_Exam.Exam_Id=FM_Achievement.Achievement_ExamId) as ExamTotalNum, (select ExamClassNum from dbo.FM_ExamAll where FM_ExamAll.Exam_Id=FM_Achievement.Achievement_ExamId and FM_ExamAll.ExamAll_GradeClassCode=FM_Achievement.Achievement_Class and FM_ExamAll.ExamAll_Subjectid=FM_Achievement.Achievement_SubjectId) as ExamClassNum, (select Subject_Name from dbo.FM_Subject where dbo.FM_Subject.Subject_Id=FM_Achievement.Achievement_SubjectId) as SubjectName, Achievement_UserId,Achievement_UserName,Achievement_Content from FM_Achievement where Achievement_UserId='100706010120' and Achievement_SubjectId=4483