SQL 查询平均分最高的系（sdept)怎么写啊,谢谢

Question

表结构是这样的：
stu（sno,sname,sdept,sage）
sc（sno,cno,grade）
cou（cno,cname）

Answer 1

SELECT sdept ,
AvgGrade
FROM ( SELECT sdept ,
AVG(grade) AS AvgGrade ,
DENSE_RANK() OVER ( PARTITION BY sdept ORDER BY AVG(grade) ) AS RN
FROM ( SELECT sdept ,
sno ,
SUM(grade) AS grade
FROM stu AS a
INNER JOIN sc AS b ON b.sno = a.sno
GROUP BY sdept ,
sno
) AS a
GROUP BY sdept
) AS T
WHERE RN = 1;