MySQL:为什么这两个是错误的?(组函数使用无效，使用 haveing() 后未选择任何内容)

Question

信用：Leetcode_1076.项目员工二这是示例表 项目表：

project_id  | employee_id 
1           | 1           
1           | 2           
1           | 3           
2           | 1           
2           | 4           

(project_id、employee_id( 是此表的主键。

我已经尝试了几种方法，并设法通过使用dense_rank((找到了正确的方法;但是，我仍然不明白为什么这两种语法选择员工人数最多的project_id是错误的：

1.返回空值： {"标头"： ["project_id"， "n"]， "值"： []}

select project_id, count(*) as n
from project
group by project_id
having max(n)

• 我期待它返回{"标题"：["project_id"，"n"]，"值"：[1,3]}。

阿拉伯数字。错误：组函数使用无效

SELECT project_id, max(count(*)) as n
from project 
group by project_id

如果有人可以帮助我，真的很感激！

Answer 1

这两种方法都有一个常见问题，即您尝试在同一级别访问 SQL 中的聚合结果。在 SQL 中不能有嵌套的聚合函数(例如.max(count(1((。

你的 SQL 应该如下 -

select t.project_id, max(t.n) from (select project_id, count(*) as n
from project
group by project_id) t;

SQLFiddle 演示 - http://sqlfiddle.com/#!9/9c9b18/4

Answer 2

使用order by和limit：

select project_id, count(*) as n
from project
group by project_id
order by n desc
limit 1;

如果要处理领带，请使用窗口函数：

select p.*
from (select project_id, count(*) as n,
rank() over (order by count(*) desc) as seqnum
from project
group by project_id
) p
where seqnum = 1;