如何获取单科最高分对应学生？SQL查询返回错误学生信息问题

需要查询特定科目下的最高分，并显示该分数对应的学生姓名。当前编写的SQL查询能获取班级内该科目的最高分，但无法匹配到正确的student_id，导致显示的学生姓名并非最高分对应的学生。

原查询代码 #

$subjectMaxQry = $this->db->query(
        "SELECT `student_id`, max(".$totField.") AS highest, 
                (SELECT firstname FROM students WHERE students.id = student_id) AS firstname, 
                (SELECT lastname FROM students WHERE students.id = student_id) AS lastname 
        FROM ".$table." 
        WHERE `session_id` = '".$sch_setting->session_id."' 
        AND `class_id` = '".$class_id."' 
        AND `subject_id` = '".$subName['sub_id']."'");

$highestMark = $subjectMaxQry->row_array();

问题原因 #

原SQL存在逻辑错误：未指定GROUP BY的情况下，将student_id与聚合函数MAX()同时放在SELECT中，数据库会返回查询结果集中任意一条记录的student_id（而非对应最高分的那条），导致关联查询到的学生姓名错误。

解决方法 #

方法1：子查询匹配最高分 #

先通过子查询获取该科目的最高分，再查询对应分数的学生记录并关联学生表：

SELECT m.student_id, s.firstname, s.lastname, m.`$totField` AS highest
FROM `$table` m
JOIN students s ON m.student_id = s.id
WHERE m.session_id = '$sch_setting->session_id'
  AND m.class_id = '$class_id'
  AND m.subject_id = '$subName[sub_id]'
  AND m.`$totField` = (
    SELECT MAX(`$totField`)
    FROM `$table`
    WHERE session_id = '$sch_setting->session_id'
      AND class_id = '$class_id'
      AND subject_id = '$subName[sub_id]'
  )

方法2：使用CodeIgniter查询构造器（更安全，避免SQL注入） #

推荐用框架查询构造器拼接SQL，规避直接变量拼接的注入风险：

// 第一步：获取该科目的最高分
$maxMark = $this->db->select_max($totField)
                    ->where('session_id', $sch_setting->session_id)
                    ->where('class_id', $class_id)
                    ->where('subject_id', $subName['sub_id'])
                    ->get($table)
                    ->row()->$totField;

// 第二步：查询对应最高分的学生信息
$subjectMaxQry = $this->db->select('m.student_id, s.firstname, s.lastname, m.'.$totField.' AS highest')
                          ->from($table.' m')
                          ->join('students s', 'm.student_id = s.id')
                          ->where('m.session_id', $sch_setting->session_id)
                          ->where('m.class_id', $class_id)
                          ->where('m.subject_id', $subName['sub_id'])
                          ->where('m.'.$totField, $maxMark)
                          ->get();

$highestMark = $subjectMaxQry->row_array();

方法3：使用窗口函数（适用于MySQL 8.0+等现代数据库） #

如果数据库支持窗口函数，可通过RANK()直接筛选最高分记录：

SELECT student_id, firstname, lastname, highest
FROM (
    SELECT m.student_id, s.firstname, s.lastname, m.`$totField` AS highest,
           RANK() OVER (ORDER BY m.`$totField` DESC) AS rnk
    FROM `$table` m
    JOIN students s ON m.student_id = s.id
    WHERE m.session_id = '$sch_setting->session_id'
      AND m.class_id = '$class_id'
      AND m.subject_id = '$subName[sub_id]'
) t
WHERE rnk = 1;

内容的提问来源于stack exchange，提问作者Esepebbles