php - mysql 获取学生考试排名

Question

我有以下查询。

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 条件放在查询中的哪里。

Answer 1

尝试将 exam_id 也放入您的子查询中。

(SELECT 
      GROUP_CONCAT(summarks 
        ORDER BY summarks DESC) 
    FROM
      (SELECT 
        SUM(
          marks_obtained_written + marks_obtained_oral + marks_obtained_classwork
        ) AS summarks 
      FROM
        results WHERE exam_id = ?
      GROUP BY student_id