sql - 以特定方式对列值进行排序

Question

我有 2 张桌子：

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


| 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     |

我想获得每个学生和每门课程的平均分数，并按学生 ID 和课程顺序（数学、物理和化学）对结果进行排序。

输出应如下所示：对于每个学生，课程顺序应为（数学、物理、化学），并且应按学生姓名排序。

| 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 |

我如何达到同样的效果？

我编写了以下查询，但无法以所需的方式对 Course n 进行排序。

select T1.[Student Name], T2.Course, 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]

有人可以帮忙吗。

标签： sqlsql-servertsql

Answer 1

您需要一些数据来告诉 SQL Server 如何对课程进行排序。

创建第三个表 T3

| course | course_order |
+--------+--------------+
| Math   | 1            |
| Phys   | 2            |
| Chem   | 3            |

那么您的查询应该如下所示：

Select T1.[Student Name], T2.Course, Avg(T2.Points as float) as 'avg (points)'
From T1
Join T2 On T1.[Student ID] = T2.[Student ID]
Join T3 On T2.[course] = T3.[course]
Group By T1.[Student ID], T1.[Student Name], T2.Course
Order By T1.[Student Name], T3.course_order