如何从每组中选择前3个最高值

Question

我有一个分数表，学生的测验分数和作业分数是按以下方式输入的

Assignment1Assignment3tbody> <<tr>245

StudentID	Quiz1	Quiz2	Quiz3	Quiz5	Assignment2	Assignment4	Assignment5	Assignment5	Assignment5	1	321	10	8	4	1	9	7	3	9	85
	321	6	10	6	3	8	4	7	1	8	6
3	321	7	92		8	4	10	75		8	3
	321	72		6	4	8	3	6	9	105
	321	3	4	5	7	105		7	8	3	9

一种方法是使用CROSS APPLY取消pivot和求和标记

SELECT StudentID, CourseID, qm.m, am.m
FROM Marks 
cross apply (
select sum (q) m
from (
select top(3) q
from (
values
(Quiz1),(Quiz2),(Quiz3),(Quiz4),(Quiz5)
) t(q)
order by q desc
) tq
) qm
cross apply (
select sum (q) m
from (
select top(3) q
from (
values
(Assignment1),(Assignment2),(Assignment3),(Assignment4),(Assignment5)
) t(q)
order by q desc
) tq
) am