oracle 与连接图的其余部分断开连接
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/28124906/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Disconnected from the rest of the join graph
提问by user4359659
My task is to query a database, selecting the number of persons that work in the most numerous department. I do the following:
我的任务是查询数据库，选择在最多部门工作的人数。我执行以下操作：
select count(*) from Persons join Departments using (Department_id)
where Department_id =
(select Department_id from Persons join Departments using (Department_id)
group by Department_id having count(*) =
(select max(count(*)) from Persons
join Departments using (Department_id) group by Department_id)
);
And it works fine, but I get a warning, that Persons is disconnected from the rest of the join graph. Is there something faulty with this solution? Or perhaps it could be done easier?
它工作正常，但我收到一个警告，即 Persons 与连接图的其余部分断开连接。这个解决方案有什么问题吗？或者也许它可以更容易地完成？
回答by Gordon Linoff
I'm not a fan of the max(count(*))construct. It is an Oracle extension and it changes the semantics of group by(which in all other cases returns one row per group). How about just:
我不喜欢这个max(count(*))结构。它是一个 Oracle 扩展，它改变了group by（在所有其他情况下每组返回一行）的语义。怎么样：
with d as (
select count(*) as cnt
from persons
group by department_id
)
select *
from d
where cnt = (select max(cnt) from d);
回答by user4359659
You are right, the join is in fact redundant. Here is my new proposed solution:
你是对的，联接实际上是多余的。这是我提出的新解决方案：
select count(*) from Persons
where Department_id is not null
group by Department_id
having count(*) = (select max(count(*)) from Persons
where Department_id is not null
group by Department_id);
