如何在sql中找到分組的平均值的最大值

我有三個表 Faculty {id, name}, Student {id, Faculty_id, name}, Course_student {id, student_id, mark}

我會選擇每個學院的優秀學生

我厭倦了以多種方式執行此操作，但我不能

SELECT Faculty.name, Student.name, MAX(AVG(mark)) 
FROM (Faculty inner join Student On Faculty.id = Student.faculty_id) inner join Course_student On Student.id = Course_student.student_id
group by Faculty.name

我無法檢查結果，但我希望下面的查詢應該有效。將來，請添加 DDL（數據定義語言），包括用於創建表的腳本，並插入一些說明預期結果的記錄。

WITH CTE AS (
SELECT Faculty.name AS Faculty_Name, 
      Student.name AS Student_Name, 
      AVG(mark) AS Avg_Mark,  
      ROW_NUMBER() OVER(PARTITION BY Faculty.name ORDER BY AVG(mark) DESC) AS Row
FROM Faculty 
INNER JOIN Student On Faculty.id = Student.faculty_id) 
INNER JOIN Course_student On Student.id = Course_student.student_id
GROUP BY Faculty.name
)
SELECT Faculty_Name, Student_Name, Avg_Mark
FROM CTE
WHERE Row = 1

引用自：https://dba.stackexchange.com/questions/225952