谁来帮我看看啊,不解ing...

Question

TableTemp 
==> 
) TableTemp

Answer 1

TotalMarkList做成一个视图，
select *,N'排名'=(select count(*) from TotalMarkList where 总分>TotalMarkList.总分) from TotalMarkList

Answer 2

select a.*,N'排名'=(select count(*) from TotalMarkList where 总分>a.总分) from TotalMarkList a

Answer 3

不能做视图，那就把TotalMarkList 用你的查询语句替换

Answer 4

不行,TotalMarkList怎么都要在声明个别名来用,一搞就错

Answer 5

--请问楼主，是不是下面的这段没有问题
SELECT *, 语文 + 数学 + 英语 + 计算机 AS 总分, (语文 + 数学 + 英语 + 计算机) 
              / 4 AS 平均分
        FROM (SELECT SU.S_StudentName AS 姓名, SU.S_StudentNum AS 学号, 
                      V_Class.S_ClassName AS 班级,
                          (SELECT T_ExamMark.N_Mark
                         FROM T_ExamMark INNER JOIN
                               T_SubjectExamInfo ON 
                               T_ExamMark.I_SubjectExamInfoID = T_SubjectExamInfo.I_ID INNER
                                JOIN
                               T_ExamInfo ON 
                               T_SubjectExamInfo.I_ExamInfoID = T_ExamInfo.I_ID INNER JOIN
                               T_Subject ON 
                               T_SubjectExamInfo.I_SubjectID = T_Subject.I_ID
                         WHERE (T_ExamInfo.I_ID = 19) AND (T_Subject.I_ID = 1) AND 
                               (T_ExamMark.S_StudentUID = SU.S_UID)) AS 语文,
                          (SELECT T_ExamMark.N_Mark
                         FROM T_ExamMark INNER JOIN
                               T_SubjectExamInfo ON 
                               T_ExamMark.I_SubjectExamInfoID = T_SubjectExamInfo.I_ID INNER
                                JOIN
                               T_ExamInfo ON 
                               T_SubjectExamInfo.I_ExamInfoID = T_ExamInfo.I_ID INNER JOIN
                               T_Subject ON 
                               T_SubjectExamInfo.I_SubjectID = T_Subject.I_ID
                         WHERE (T_ExamInfo.I_ID = 19) AND (T_Subject.I_ID = 2) AND 
                               (T_ExamMark.S_StudentUID = SU.S_UID)) AS 数学,
                          (SELECT T_ExamMark.N_Mark
                         FROM T_ExamMark INNER JOIN
                               T_SubjectExamInfo ON 
                               T_ExamMark.I_SubjectExamInfoID = T_SubjectExamInfo.I_ID INNER
                                JOIN
                               T_ExamInfo ON 
                               T_SubjectExamInfo.I_ExamInfoID = T_ExamInfo.I_ID INNER JOIN
                               T_Subject ON 
                               T_SubjectExamInfo.I_SubjectID = T_Subject.I_ID
                         WHERE (T_ExamInfo.I_ID = 19) AND (T_Subject.I_ID = 8) AND 
                               (T_ExamMark.S_StudentUID = SU.S_UID)) AS 英语,
                          a AS 计算机
                FROM T_StudentUser SU INNER JOIN
                      V_Class ON SU.I_ClassID = V_Class.I_ID
                WHERE (V_Class.I_GradeID = 1)) MarkList--生成一个查询表叫做TotalMarkList
--下面再进行排名：
--在上面查询语句两头加上括号,如下所示： (SELECT *, 语文 + 数学 + 英语 + 计算机 AS 总分, (语文 + 数学 + 英语 + 计算机) 
              / 4 AS 平均分
        FROM (SELECT SU.S_StudentName AS 姓名, SU.S_StudentNum AS 学号, 
                      V_Class.S_ClassName AS 班级,
                          (SELECT T_ExamMark.N_Mark
                         FROM T_ExamMark INNER JOIN
                               T_SubjectExamInfo ON 
                               T_ExamMark.I_SubjectExamInfoID = T_SubjectExamInfo.I_ID INNER
                                JOIN
                               T_ExamInfo ON 
                               T_SubjectExamInfo.I_ExamInfoID = T_ExamInfo.I_ID INNER JOIN
                               T_Subject ON 
                               T_SubjectExamInfo.I_SubjectID = T_Subject.I_ID
                         WHERE (T_ExamInfo.I_ID = 19) AND (T_Subject.I_ID = 1) AND 
                               (T_ExamMark.S_StudentUID = SU.S_UID)) AS 语文,
                          (SELECT T_ExamMark.N_Mark
                         FROM T_ExamMark INNER JOIN
                               T_SubjectExamInfo ON 
                               T_ExamMark.I_SubjectExamInfoID = T_SubjectExamInfo.I_ID INNER
                                JOIN
                               T_ExamInfo ON 
                               T_SubjectExamInfo.I_ExamInfoID = T_ExamInfo.I_ID INNER JOIN
                               T_Subject ON 
                               T_SubjectExamInfo.I_SubjectID = T_Subject.I_ID
                         WHERE (T_ExamInfo.I_ID = 19) AND (T_Subject.I_ID = 2) AND 
                               (T_ExamMark.S_StudentUID = SU.S_UID)) AS 数学,
                          (SELECT T_ExamMark.N_Mark
                         FROM T_ExamMark INNER JOIN
                               T_SubjectExamInfo ON 
                               T_ExamMark.I_SubjectExamInfoID = T_SubjectExamInfo.I_ID INNER
                                JOIN
                               T_ExamInfo ON 
                               T_SubjectExamInfo.I_ExamInfoID = T_ExamInfo.I_ID INNER JOIN
                               T_Subject ON 
                               T_SubjectExamInfo.I_SubjectID = T_Subject.I_ID
                         WHERE (T_ExamInfo.I_ID = 19) AND (T_Subject.I_ID = 8) AND 
                               (T_ExamMark.S_StudentUID = SU.S_UID)) AS 英语,
                          a AS 计算机
                FROM T_StudentUser SU INNER JOIN
                      V_Class ON SU.I_ClassID = V_Class.I_ID
                WHERE (V_Class.I_GradeID = 1)) MarkList)TotalMarkList--直接再在头加上：
SELECT *,
          (SELECT COUNT(1)
         FROM TotalMarkList
         WHERE 总分 > TableTemp.总分) AS 排名
FROM 
--不就对了吗