查询以显示具有最大值 SQL 的所有行

Question

我正在编写一个查询来计算每个工作人员监督的学生人数

SELECT   FACULTY.F_FIRST || ' ' || F_LAST AS STAFF,(COUNT(FACULTY.F_FIRST || ' ' || F_LAST)) as STUDENTCOUNT
FROM STUDENT
INNER JOIN FACULTY 
ON STUDENT.F_ID=FACULTY.F_ID

GROUP BY FACULTY.F_FIRST,FACULTY.F_LAST
;

正确输出

Teresa Marx 3
Colin Langley   1
Jonnel Brown    1
Mark Zhulin 1

我现在想修改它，以便查询仅显示工作人员监督最多学生（包括多名教职员工）的行，例如，如果两名教职员工有 3 名学生，他们都会显示

我以为我可以通过 HAVING 条款来做到这一点

 HAVING ((COUNT(FACULTY.F_FIRST || ' ' || F_LAST))) 
= MAX(COUNT(FACULTY.F_FIRST || ' ' || F_LAST))

但这告诉我我有一个无效的关系运算符。我相信 have 子句的第一部分是正确的，因为我可以使用它来查找特定的行值，但不是最大值

Answer 1

您可以使用

DENSE_RANK()或RANK()执行此操作。 下面是一个示例：

SELECT (f.F_FIRST || ' ' || f.F_LAST) AS STAFF, CNT as STUDENTCOUNT
FROM FACULTY f INNER JOIN
     (SELECT s.F_ID, COUNT(*) as CNT,
             DENSE_RANK() OVER (ORDER BY COUNT(*) DESC) as seqnum
      FROM STUDENT s
      GROUP BY s.F_ID
     ) s
     ON s.F_ID = f.F_ID
WHERE seqnum = 1;

此公式假定名字/姓氏对于给定F_ID是唯一的。

另请注意，使用表别名时，查询更易于编写和读取。