如何为同一列的COUNT获取表中具有MAX值的所有行

Question

student_id

dept_id	course_id
CS	101	11
数学	101	11
CS	101	12
CS	201	22
数学	301	22
EE	102	33
数学	201	33

您可以使用DENSE_RANK函数根据注册学生的数量为每个系指定一个排名，如下所示：

SELECT dept_id
FROM
(
SELECT dept_id, DENSE_RANK() OVER (ORDER BY COUNT(*) DESC) rnk
FROM enrolled
GROUP BY dept_id
) T
WHERE rnk=1 

查看演示。

如果您有不支持windows函数的旧MySQL版本，则可以使用HAVING COUNT子句

select dept_id 
from  enrolled 
group by dept_id
having count(dept_id) =  ( select max(tot_count) 
from ( select count(dept_id) as tot_count 
from  enrolled 
group by dept_id
) tbl
);

https://dbfiddle.uk/sNcMzVxe

以下查询将返回最大计数，然后在have子句中使用该计数

select max(tot_count) 
from ( select count(dept_id) as tot_count 
from  enrolled 
group by dept_id
) tbl

简单地说，在你的情况下，它将是having count(dept_id) = 3;

• 首先，您需要根据每个部门计算总分，我们在CTE 1中使用GROUP BY进行了计算
• 那么你需要根据最高分数(降序(创建排名，如果分数可以打平，你需要根据DENSE_RANK()创建排名。这将始终为相同的值生成相同的秩，在您的情况下，它将查看步骤1中最高顺序的total_scores(ORDER BY设置为desc(。如果你想以其他方式进行查询，你可以删除DESC，默认情况下窗口函数将按ASC的顺序工作
• 最后，您使用where子句筛选rank=1以获得最高注册部门

with main as (
select dept_id, count(student_id) as total_enrolled_students
from enrolled
group by 1
),ranking as (
select *, 
dense_rank() over(order by total_enrolled_students desc) as rank_
from main
)
select * from ranking where rank_ = 1;