PHP与MySQL：高效查询与排序学生科目成绩，选取最佳表现科目

<td>
<?php
    $t = $res['subject_marks'];
    if ($t >= 75) {
        echo $res['subject_marks'] . " - A";
    } else if ($t >= 65) {
        echo $res['subject_marks'] . " - B";
    } else if ($t >= 45) {
        echo $res['subject_marks'] . " - C";
    } else if ($t >= 30) {
        echo $res['subject_marks'] . " - D";
    } else if ($t > 0) {
        echo $res['subject_marks'] . " - F";
    } else if ($t < 0) {
        echo ""; // 处理负分情况
    }
?>
</td>

SELECT * FROM tbl_student_primary_subject
INNER JOIN tbl_primary_subject ON tbl_primary_subject.subject_id = tbl_student_primary_subject.subject_id
WHERE tbl_student_primary_subject.student_id='$sudentid'

SELECT column1, column2, ...
FROM table_name
ORDER BY column_name ASC|DESC;

SELECT
    tsp.subject_id,
    tp.subject_name,
    tp.subject_code,
    tsp.marks AS subject_marks -- 为成绩字段添加别名，使结果更清晰
FROM
    tbl_student_primary_subject tsp
INNER JOIN
    tbl_primary_subject tp ON tp.subject_id = tsp.subject_id
WHERE
    tsp.student_id = '$sudentid'
ORDER BY
    tsp.marks DESC; -- 按学生科目成绩降序排列

SELECT column1, column2, ...
FROM table_name
LIMIT number;

SELECT column1, column2, ...
FROM table_name
LIMIT offset, number;

SELECT
    tsp.subject_id,
    tp.subject_name,
    tp.subject_code,
    tsp.marks AS subject_marks
FROM
    tbl_student_primary_subject tsp
INNER JOIN
    tbl_primary_subject tp ON tp.subject_id = tsp.subject_id
WHERE
    tsp.student_id = '$sudentid'
ORDER BY
    tsp.marks DESC
LIMIT 7; -- 选取成绩最高的7个科目

<?php
// 假设 $con 是你的数据库连接对象，例如通过 mysqli 或 PDO 初始化
// 假设 $sudentid 已经从用户输入或其他来源获取

// **重要：防止SQL注入！**
// 以下示例使用 mysqli_real_escape_string 进行简单转义，但强烈推荐使用预处理语句（Prepared Statements）。
// 例如：$sudentid = $con->real_escape_string($sudentid);

// 优化的SQL查询，获取指定学生成绩最高的7门科目
$tbl_student_subject_query = "
    SELECT
        tsp.subject_id,
        tp.subject_name,
        tp.subject_code,
        tsp.marks AS subject_marks
    FROM
        tbl_student_primary_subject tsp
    INNER JOIN
        tbl_primary_subject tp ON tp.subject_id = tsp.subject_id
    WHERE
        tsp.student_id = ? -- 使用占位符，配合预处理语句
    ORDER BY
        tsp.marks DESC
    LIMIT 7;
";

// 推荐使用预处理语句 (mysqli 示例)
$stmt = $con->prepare($tbl_student_subject_query);
if ($stmt === false) {
    die("Prepare failed: " . $con->error);
}

// 绑定参数
$stmt->bind_param("s", $sudentid); // "s" 表示参数类型为字符串

// 执行查询
$stmt->execute();

// 获取结果集
$results = $stmt->get_result();

if ($results && $results->num_rows > 0) {
    echo "<h2>学生ID: " . htmlspecialchars($sudentid) . " 的前7门最佳科目成绩</h2>";
    echo "<table border='1' style='width:100%; border-collapse: collapse;'>";
    echo "<thead><tr><th>科目名称</th><th>科目代码</th><th>成绩</th><th>等级</th></tr></thead>";
    echo "<tbody>";
    while ($res = $results->fetch_assoc()) {
        $subject_marks = $res['subject_marks'];
        $grade = '';
        // 根据成绩判断等级的PHP逻辑
        if ($subject_marks >= 75) {
            $grade = "A";
        } else if ($subject_marks >= 65) {
            $grade = "B";
        } else if ($subject_marks >= 45) {
            $grade = "C";
        } else if ($subject_marks >= 30) {
            $grade = "D";
        } else if ($subject_marks > 0) {
            $grade = "F";
        }
        // 对于 $subject_marks < 0 的情况，原代码输出空。
        // 如果不希望负分显示，可以在SQL的WHERE子句中添加条件 `AND tsp.marks >= 0`

        echo "<tr>";
        echo "<td>" . htmlspecialchars($res['subject_name']) . "</td>";
        echo "<td>" . htmlspecialchars($res['subject_code']) . "</td>";
        echo "<td>" . htmlspecialchars($subject_marks) . "</td>";
        echo "<td>" . htmlspecialchars($grade) . "</td>";
        echo "</tr>";
    }
    echo "</tbody>";
    echo "</table>";
} else {
    echo "<p>未找到学生ID: " . htmlspecialchars($sudentid) . " 的科目成绩或没有足够数据。</p>";
}

// 关闭语句和结果集
$stmt->close();
$results->close();
// $con->close(); // 在适当的时候关闭数据库连接
?>