我有以下查询。
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条件放在查询中。
最佳答案
尝试也将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