我有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
我如何實作相同的目標？
我撰寫了以下查詢，但無法按所需方式對課程進行排序。
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]
有人可以幫忙嗎。
uj5u.com熱心網友回復：
您需要一些資料來告訴 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
uj5u.com熱心網友回復：
每當有基于某些字串或某些值的給定所需序列時，正常情況下無法實作，order by asc或者order by desc
您需要通過case when then在您的order by子句中使用來進行策劃排序。像這樣的東西
order by T1.[Student Name]
, case T2.Course when 'Math' then 1
when 'Phys' then 2
when 'Chem' then 3
else 4 end
所以你的整個查詢看起來像這樣。
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]
, case T2.Course when 'Math' then 1
when 'Phys' then 2
when 'Chem' then 3
else 4 end
uj5u.com熱心網友回復：
嘗試這個
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], T2.Course
uj5u.com熱心網友回復：
您可以使用 CASE 運算式進行排序。例如：
select ...
from ...
where ...
order by T1.[Student Name],
case T2.course when 'Math' then 1 when 'Phys' then 2 else 3 end
;
注意 - 保持一致。您的表格中不存在“數學、物理和化學”。您可能還會注意到這不是很靈活。將另一門課程（例如，Bio）添加到您的表中，會發生什么？
轉載請註明出處，本文鏈接：https://www.uj5u.com/qukuanlian/357317.html
標籤：sql sql-server 查询语句
下一篇：選擇具有兩個簡單特征的所有不同行