开发校园管理系统：学生成绩报告MySQL查询需求及问题求助

需求说明 #

• 针对指定session_id、class_id和subject_id生成学生成绩报告
• 按规则计算学生累计成绩（用于排名），同时统计该科目下的最高、最低分
• 累计成绩计算规则：
  • 若学生无第一或第二section记录：将第三section与存在的section的总分（ca1+ca2+exam）之和除以2
  • 若三个section均有记录：将第一、第二section的总分之和除以3
  • 若仅第三section有记录：直接取该section的总分作为累计成绩

现有数据表结构 #

tbl_student_class（学生班级关联表） #

字段：st_cl_id、admission_number、session_id、section_id、class_id、date

tbl_result（学生成绩表） #

字段：result_id、ca1、ca2、exam、section_id、session_id、class_id、subject_id

原查询问题 #

原查询未正确实现累计成绩计算逻辑，且未覆盖预期输出的核心字段，具体问题：

• IFNULL逻辑无效：SUM()返回数值（无匹配时为0），不会返回NULL，导致分支逻辑不触发
• 未处理第三section的成绩计算场景
• 缺少科目名称、各单项成绩、等级、排名、最高/最低分等字段
• JOIN条件错误：将subject_id过滤放在JOIN环节，应放在WHERE子句中

原查询代码：

SELECT 
    s.name AS student_name, 
    s.admission_number,
    IFNULL((
        (SUM(CASE WHEN r.section_id = 1 THEN r.ca1 + r.ca2 + r.exam ELSE 0 END)
        + SUM(CASE WHEN r.section_id = 2 THEN r.ca1 + r.ca2 + r.exam ELSE 0 END)) / 2
    ), (
        (SUM(CASE WHEN r.section_id = 1 THEN r.ca1 + r.ca2 + r.exam ELSE 0 END)
        + SUM(CASE WHEN r.section_id = 2 THEN r.ca1 + r.ca2 + r.exam ELSE 0 END)) / 3
    )) AS cumulative_score
FROM tbl_result r
JOIN tbl_student_class sc ON r.session_id = sc.session_id 
    AND r.class_id = sc.class_id 
    AND r.section_id = sc.section_id 
    AND r.subject_id = <your_subject_id_here>
JOIN tbl_student s ON sc.admission_number = s.admission_number
WHERE r.session_id = <your_session_id_here> 
    AND r.class_id = <your_class_id_here>
GROUP BY sc.admission_number
ORDER BY cumulative_score DESC;

修正后的查询语句 #

假设存在tbl_subject表存储科目名称（字段subject_id、subject_name），以下查询实现完整需求：

WITH student_scores AS (
    SELECT
        s.admission_number,
        s.name AS student_name,
        sub.subject_name AS SUBJECT,
        -- 提取各section的单项及总分
        SUM(CASE WHEN r.section_id = 1 THEN r.ca1 ELSE 0 END) AS CA1_T1,
        SUM(CASE WHEN r.section_id = 1 THEN r.ca2 ELSE 0 END) AS CA2_T1,
        SUM(CASE WHEN r.section_id = 1 THEN r.exam ELSE 0 END) AS EXAM_T1,
        SUM(CASE WHEN r.section_id = 1 THEN r.ca1 + r.ca2 + r.exam ELSE 0 END) AS TERM_1,
        SUM(CASE WHEN r.section_id = 2 THEN r.ca1 ELSE 0 END) AS CA1_T2,
        SUM(CASE WHEN r.section_id = 2 THEN r.ca2 ELSE 0 END) AS CA2_T2,
        SUM(CASE WHEN r.section_id = 2 THEN r.exam ELSE 0 END) AS EXAM_T2,
        SUM(CASE WHEN r.section_id = 2 THEN r.ca1 + r.ca2 + r.exam ELSE 0 END) AS TERM_2,
        SUM(CASE WHEN r.section_id = 3 THEN r.ca1 ELSE 0 END) AS CA1_T3,
        SUM(CASE WHEN r.section_id = 3 THEN r.ca2 ELSE 0 END) AS CA2_T3,
        SUM(CASE WHEN r.section_id = 3 THEN r.exam ELSE 0 END) AS EXAM_T3,
        SUM(CASE WHEN r.section_id = 3 THEN r.ca1 + r.ca2 + r.exam ELSE 0 END) AS TERM_3,
        -- 统计有效section数量
        COUNT(DISTINCT CASE WHEN r.section_id IN (1,2) THEN r.section_id END) AS t12_count,
        COUNT(DISTINCT r.section_id) AS total_terms
    FROM tbl_result r
    JOIN tbl_student_class sc 
        ON r.session_id = sc.session_id 
        AND r.class_id = sc.class_id 
        AND r.section_id = sc.section_id
    JOIN tbl_student s ON sc.admission_number = s.admission_number
    JOIN tbl_subject sub ON r.subject_id = sub.subject_id
    WHERE 
        r.session_id = <your_session_id_here> 
        AND r.class_id = <your_class_id_here>
        AND r.subject_id = <your_subject_id_here>
    GROUP BY s.admission_number, s.name, sub.subject_name
),
calculated_scores AS (
    SELECT
        *,
        -- 计算累计成绩
        CASE
            WHEN total_terms = 1 AND TERM_3 > 0 THEN TERM_3
            WHEN total_terms = 3 THEN ROUND((TERM_1 + TERM_2) / 3, 2)
            ELSE CASE
                WHEN t12_count = 1 THEN ROUND((COALESCE(TERM_1, TERM_2) + TERM_3) / 2, 2)
                WHEN t12_count = 0 THEN TERM_3
            END
        END AS CUM,
        -- 提取当前展示的单项成绩及总分
        CASE
            WHEN TERM_1 > 0 THEN CA1_T1 WHEN TERM_2 > 0 THEN CA1_T2 ELSE CA1_T3 END AS CA1,
            WHEN TERM_1 > 0 THEN CA2_T1 WHEN TERM_2 > 0 THEN CA2_T2 ELSE CA2_T3 END AS CA2,
            WHEN TERM_1 > 0 THEN EXAM_T1 WHEN TERM_2 > 0 THEN EXAM_T2 ELSE EXAM_T3 END AS EXAM,
            WHEN TERM_1 > 0 THEN TERM_1 WHEN TERM_2 > 0 THEN TERM_2 ELSE TERM_3 END AS TOTAL
    FROM student_scores
),
ranked_scores AS (
    SELECT
        *,
        RANK() OVER (ORDER BY CUM DESC) AS POS_NUM,
        MAX(CUM) OVER () AS `HIGHEST SCORE`,
        MIN(CUM) OVER () AS `LOWEST SCORE`,
        COUNT(*) OVER () AS `OUT OF`
    FROM calculated_scores
)
SELECT
    SUBJECT,
    CA1,
    CA2,
    EXAM,
    TOTAL,
    TERM_1,
    TERM_2,
    CUM AS `CUM.`,
    -- 映射等级
    CASE
        WHEN CUM >= 80 THEN 'A'
        WHEN CUM >= 70 THEN 'B'
        WHEN CUM >= 60 THEN 'C'
        WHEN CUM >= 50 THEN 'P'
        ELSE 'F'
    END AS GRADE,
    -- 格式化排名后缀
    CONCAT(POS_NUM, 
        CASE 
            WHEN POS_NUM % 10 = 1 AND POS_NUM % 100 != 11 THEN 'st'
            WHEN POS_NUM % 10 = 2 AND POS_NUM % 100 != 12 THEN 'nd'
            WHEN POS_NUM % 10 = 3 AND POS_NUM % 100 != 13 THEN 'rd'
            ELSE 'th'
        END) AS POSITION,
    `OUT OF`,
    `HIGHEST SCORE`,
    `LOWEST SCORE`,
    -- 生成评语
    CASE
        WHEN CUM >= 80 THEN 'Excellent'
        WHEN CUM >= 70 THEN 'V. Good'
        WHEN CUM >= 60 THEN 'Good'
        WHEN CUM >= 50 THEN 'Fair'
        ELSE 'Failed'
    END AS COMMENT
FROM ranked_scores
ORDER BY POS_NUM;

关键说明 #

1. 累计成绩计算：通过CTE分步骤统计各section成绩，严格匹配规则判断计算逻辑
2. 排名与统计：使用窗口函数RANK()、MAX()、MIN()实现排名和全局分数统计
3. 字段适配：自动匹配有成绩的section展示单项成绩，确保与预期输出格式一致
4. 等级与评语：根据累计分区间映射对应等级和评语，支持自定义调整区间

预期输出示例 #

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