SQL查询如何按学生姓名及自定义课程顺序（Math/Phys/Chem）排序

现有两张业务表如下：

表1：学生表 T1 #

Student ID   Student Name
-------------------------
12           John Smith
14           Raj Sharma
15           Lee Wang
16           Anan Obi

表2：成绩表 T2 #

| name       | course | avg (points) |
+------------+--------+--------------+
| Anan Obi   | Math   | 96.50000     |
| Anan Obi   | Phys   | 58.00000     |
| John Smith | Math   | 86.00000     |
| John Smith | Phys   | 63.00000     |
| John Smith | Chem   | 92.50000     |
| Lee Wang   | Phys   | 78.50000     |
| Lee Wang   | Chem   | 65.00000     |
| Raj Sharma | Math   | 75.00000     |
| Raj Sharma | Phys   | 78.00000     |
| Raj Sharma | Chem   | 83.00000     |

需求说明 #

查询每个学生每门课程的平均分，查询结果优先按学生姓名排序，同一名学生的课程需按照Math>Phys>Chem的固定顺序排列。

你当前编写的代码仅能实现按学生姓名排序，且存在语法错误：avg函数内不能直接写类型转换逻辑，需调整写法。

要实现自定义顺序的课程排序，可以通过CASE WHEN为不同课程分配排序权重，将权重加入ORDER BY子句即可实现需求。修改后的完整SQL语句如下：

SELECT 
    T1.[Student Name] AS name,
    T2.Course,
    CAST(AVG(T2.Points) AS FLOAT) AS 'avg (points)'
FROM T1
JOIN T2 ON T1.[Student ID] = T2.[Student ID]
GROUP BY T1.[Student ID], T1.[Student Name], T2.Course
ORDER BY 
    T1.[Student Name],
    CASE T2.Course
        WHEN 'Math' THEN 1
        WHEN 'Phys' THEN 2
        WHEN 'Chem' THEN 3
        ELSE 4
    END ASC

如果使用的是支持FIELD函数的数据库（如MySQL），还可以简化排序逻辑的写法：

ORDER BY T1.[Student Name], FIELD(T2.Course, 'Math', 'Phys', 'Chem') ASC

逻辑说明 #

CASE WHEN/FIELD会为每个课程匹配对应的数字权重，Math权重最高（值最小）、Chem权重最低（值最大），排序时同一学生的记录会按照权重升序排列，刚好匹配要求的课程顺序。

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