如何将GPA最高的部门从GPA表和GPA列表中列出？

Question

我能做一张表，显示每个部门的平均绩点，但我不知道如何使它只显示GPA最高的部门。

我的问题是：

SELECT avg(grade) as GPA, deptID from tblStudent s inner join tblTranscript t
ON t.studentid = s.studentid
group by deptID

Answer 1

为MYSQL尝试以下操作

SELECT deptID, GPA
FROM(
    SELECT deptID, AVG(grade) AS GPA 
    FROM tblStudent s 
    INNER JOIN tblTranscript t ON t.studentid = s.studentid
    GROUP BY deptID ) t ORDER BY GPA  DESC LIMIT 1

如果使用SQL-SERVER，则为。

SELECT TOP 1 deptID, GPA
FROM(
    SELECT deptID, AVG(grade) AS GPA 
    FROM tblStudent s 
    INNER JOIN tblTranscript t ON t.studentid = s.studentid
    GROUP BY deptID ) t ORDER BY GPA DESC

Answer 2

有两种方法

或

SELECT MAX(avg(grade)) as GPA, deptID from tblStudent s inner join tblTranscript t
ON t.studentid = s.studentid
group by deptID

或

就像@ughai在这条评论里说的

SELECT avg(grade) as GPA, deptID from tblStudent s inner join tblTranscript t
ON t.studentid = s.studentid
group by deptID ORDER BY avg(grade) DESC LIMIT 1