你可以做这样的事情
SELECT StudentID, Year, Subject, AVG(Testscore) AS Avgscore
FROM
(
SELECT StudentID, Year, Subject, Testscore
FROM MyTable t
WHERE TestID IN
(
SELECT TOP 3 TestID
FROM MyTable
WHERE StudentID = t.StudentID
AND Year = t.Year
AND Subject = t.Subject
ORDER BY Testscore DESC, TestID
)
) q
GROUP BY StudentID, Year, Subject
ORDER BY StudentID, Year, Subject;
样本输出：
| STUDENTID | YEAR | SUBJECT | AVGscore |
|-----------|------|---------|----------|
| 1 | 2012 | 1 | 91 |
| 1 | 2012 | 2 | 84 |
| 2 | 2012 | 1 | 94 |
| 2 | 2012 | 3 | 95 |
602392714
清零编程群