SQL如何关联两次查询结果统计每门课程的Min、Max、Avg分数

解决方案 #

最终查询语句 #

SELECT 
    t2.CourseName,
    t2.TeacherName,
    t2.SemName,
    MIN(t1.MarksObtained) AS Min,
    MAX(t1.MarksObtained) AS Max,
    AVG(t1.MarksObtained) AS Avg
FROM 
    (
        -- 第一条查询结果，存储课程、学生、分数对应关系
        SELECT 
            CourseName, StdID, MarksObtained 
        FROM 
            stdmarks
        INNER JOIN 
            course ON course.courseid = stdmarks.examid
    ) AS t1
INNER JOIN 
    (
        -- 第二条查询结果，存储课程、授课老师、学期对应关系
        SELECT DISTINCT 
            CourseName, TeacherName, SemName 
        FROM 
            teacher
        INNER JOIN 
            stdcourseteacher ON teacher.teacherid = stdcourseteacher.teacherid
        INNER JOIN 
            course ON course.courseid = stdcourseteacher.courseid
        INNER JOIN 
            semester ON stdcourseteacher.semid = semester.semid
    ) AS t2 ON t1.CourseName = t2.CourseName
GROUP BY 
    t2.CourseName, t2.TeacherName, t2.SemName

逻辑说明 #

• 将你原有两条查询分别作为临时表t1、t2复用，不需要单独修改原有查询逻辑
• 以CourseName作为关联键拼接两张临时表的字段
• 按课程名、教师名、学期三个维度分组，对分数字段分别做最小、最大、平均值聚合即可得到目标结果

提示：如果存在重名课程的情况，建议将关联条件改为唯一标识CourseID，避免关联错误，统计结果会更准确。

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