优化学生科目成绩排序与筛选：SQL与PHP实践

ORDER BY marks DESC

LIMIT 7

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
    tsp.subject_id,
    tsp.marks AS subject_marks,       -- 学生在该科目获得的具体分数
    tps.subject_name,                 -- 科目名称
    tps.subject_code                  -- 科目代码
FROM
    tbl_student_primary_subject tsp   -- 学生-科目关联表，通常包含学生ID、科目ID和分数
INNER JOIN
    tbl_primary_subject tps ON tps.subject_id = tsp.subject_id -- 科目信息表
WHERE
    tsp.student_id = '$sudentid'      -- 根据学生ID筛选
ORDER BY
    tsp.marks DESC                    -- 按科目分数从高到低排序
LIMIT 7;                              -- 只选取分数最高的7门科目

<?php
// 假设 $con 是数据库连接对象
// 假设 $sudentid 已经通过安全方式获取并处理（例如，使用预处理语句）

// 构建SQL查询，已包含排序和限制
$tbl_student_subject_query = "
    SELECT
        tsp.subject_id,
        tsp.marks AS subject_marks,
        tps.subject_name,
        tps.subject_code
    FROM
        tbl_student_primary_subject tsp
    INNER JOIN
        tbl_primary_subject tps ON tps.subject_id = tsp.subject_id
    WHERE
        tsp.student_id = ?  -- 使用占位符防止SQL注入
    ORDER BY
        tsp.marks DESC
    LIMIT 7;
";

// 使用预处理语句执行查询，提高安全性
$stmt = $con->prepare($tbl_student_subject_query);
$stmt->bind_param("s", $sudentid); // 假设 student_id 是字符串类型
$stmt->execute();
$results = $stmt->get_result();

$subjects_results = [];
$total_marks = 0;
$num_subjects = 0;

// 遍历查询结果，这些结果已经是排序好且限制了数量的
while ($row = $results->fetch_assoc()) {
    $subjects_results[] = [
        'subject_name'  => $row['subject_name'],
        'subject_code'  => $row['subject_code'],
        'subject_marks' => $row['subject_marks'],
    ];
    $total_marks += $row['subject_marks'];
    $num_subjects++;
}

// 计算平均分（如果需要，基于这7门科目）
$avg_marks = ($num_subjects > 0) ? ($total_marks / $num_subjects) : 0;

// 在HTML中显示结果
?>
<table>
    <thead>
        <tr>
            <th>科目名称</th>
            <th>科目代码</th>
            <th>分数与等级</th>
        </tr>
    </thead>
    <tbody>
        <?php foreach ($subjects_results as $res): ?>
            <tr>
                <td><?php echo htmlspecialchars($res['subject_name']); ?></td>
                <td><?php echo htmlspecialchars($res['subject_code']); ?></td>
                <td>
                    <?php
                    $t = $res['subject_marks'];
                    if ($t >= 75) {
                        echo htmlspecialchars($t) . " - A";
                    } else if ($t >= 65) {
                        echo htmlspecialchars($t) . " - B";
                    } else if ($t >= 45) {
                        echo htmlspecialchars($t) . " - C";
                    } else if ($t >= 30) {
                        echo htmlspecialchars($t) . " - D";
                    } else if ($t > 0) {
                        echo htmlspecialchars($t) . " - F";
                    } else if ($t <= 0) { // 修正：当分数小于等于0时，显示为空或特定标记
                        echo "";
                    }
                    ?>
                </td>
            </tr>
        <?php endforeach; ?>
    </tbody>
</table>