我有以下查询。

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作为学生等级。

php - mysql在考试中获得学生排名-LMLPHP

在上图中,我获得了第0个位置,这是无效的。但是,如果我删除了exam_id条件,那么我得到了正确的排名,但是问题是它对所有我不想输入的考试加了分数,因为我需要特定考试的排名。

因此,请任何人告诉我我应该在哪里将exam_id条件放在查询中。

最佳答案

尝试也将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

10-08 06:58
查看更多