SQL如何计算每位学生每门课程的平均分数并按姓名排序

问题分析 #

你编写的SQL存在几个核心问题：

• 未指定两张表的关联条件，直接并列查询会产生笛卡尔积，数据完全错乱，需要通过Student ID字段关联两张表
• GROUP BY字段不符合需求：要计算每位学生每门课的平均分，需要同时按学生、课程两个维度分组，原SQL仅按课程分组，会得到所有学生该课程的整体平均分，不符合要求
• 语法错误：带空格的字段名需要用反引号`包裹，不能用单引号，单引号包裹的内容会被识别为字符串常量，无法正确读取字段值
• 没有处理课程名缩写和列名别名，和预期输出格式不符

正确实现逻辑 #

1. 关联Table1和Table2，关联条件为两张表的Student ID相等，拿到每个学生的姓名、对应课程和分数
2. 按照学生ID（避免重名问题）、学生姓名、课程三个字段分组，对每组的Points计算平均值
3. 对课程名做缩写映射：Mathematics转Math，Physics转Phys，Chemistry转Chem
4. 对平均分保留5位小数，设置对应列别名，最后按学生姓名排序输出

正确SQL语句 #

SELECT 
    t1.`Student Name` AS `name`,
    CASE t2.`Course`
        WHEN 'Mathematics' THEN 'Math'
        WHEN 'Physics' THEN 'Phys'
        WHEN 'Chemistry' THEN 'Chem'
    END AS `course`,
    ROUND(AVG(t2.`Points`), 5) AS `avg (points)`
FROM 
    Table1 t1
INNER JOIN 
    Table2 t2 ON t1.`Student ID` = t2.`Student ID`
GROUP BY 
    t1.`Student ID`, t1.`Student Name`, t2.`Course`
ORDER BY 
    t1.`Student Name` ASC;

注：将Student ID加入GROUP BY是为了兼容严格SQL模式的数据库，避免因学生重名导致分组错误，同时符合大部分数据库的GROUP BY语法要求。

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