问题描述
- SQL 查询平均分最高的系(sdept)怎么写啊,谢谢
-
表结构是这样的:
stu(sno,sname,sdept,sage)
sc(sno,cno,grade)
cou(cno,cname)
解决方案
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;
解决方案二:
用以上语句去测测,提示:相同的贴发一贴就行了
时间: 2024-08-03 10:31:35