mysql在考试中获得学生排名(mysql get student rank in exam)

 我有以下查询。 
 
SELECT 
  SUM(
    marks_obtained_written + marks_obtained_oral + marks_obtained_classwork
  ),
  FIND_IN_SET(
    SUM(
      marks_obtained_written + marks_obtained_oral + marks_obtained_classwork
    ),
    (SELECT 
      GROUP_CONCAT(summarks 
        ORDER BY summarks DESC) 
    FROM
      (SELECT 
        SUM(
          marks_obtained_written + marks_obtained_oral + marks_obtained_classwork
        ) AS summarks 
      FROM
        results 
      GROUP BY student_id) sm)
  ) AS rank 
FROM
  results 
WHERE student_id = ".$student_id." 
  AND exam_year = ".$year." 
  AND exam_id = ".$exam." 
  AND results.class_id = ".$class_id." 
  AND section_id = ".$section_id." 
 
 只有在我的结果表中有一个考试时，此查询才适用于我。 当有更多考试时，此查询会计算所有考试的分数并返回给我。 但是如果我只需要一个考试的学生排名，我需要在查询中放入exam_id，当我这样做时，查询返回0作为学生排名。 
 
  
 
 在上面的图片中，我得到了第0个无效的位置。但是如果我删除了exam_id条件，那么我得到了正确的等级，但问题是所有输入的考试的总和标记，我不想要因为我需要特定考试的等级。 
 
 所以请任何人告诉我应该在我的查询中放置exam_id条件。 

I have the following query.
 
SELECT 
  SUM(
    marks_obtained_written + marks_obtained_oral + marks_obtained_classwork
  ),
  FIND_IN_SET(
    SUM(
      marks_obtained_written + marks_obtained_oral + marks_obtained_classwork
    ),
    (SELECT 
      GROUP_CONCAT(summarks 
        ORDER BY summarks DESC) 
    FROM
      (SELECT 
        SUM(
          marks_obtained_written + marks_obtained_oral + marks_obtained_classwork
        ) AS summarks 
      FROM
        results 
      GROUP BY student_id) sm)
  ) AS rank 
FROM
  results 
WHERE student_id = ".$student_id." 
  AND exam_year = ".$year." 
  AND exam_id = ".$exam." 
  AND results.class_id = ".$class_id." 
  AND section_id = ".$section_id." 
 
This query works fine for me only when there in one exam in my result table. When there are more exams,this query calculates marks for all exams and return rank to me. But In case I need student rank for only one exam, I need to put exam_id in the query and when I do that, the query returns 0 as student rank. 
 
 
In above picture, I got 0th position which is invalid.But if i remove the exam_id condition,then I get the correct rank,but the problem is it sums marks for all entered exams which i dont want as I need rank for a specific exam.
 
So please anyone tell me where should I put the exam_id condition in my query.

原文：https://stackoverflow.com/questions/43178310