如何修复组函数嵌套过深的错误

Question

我在学习SQL时遇到了一个从未见过的错误，它说我的函数分组嵌套太深了？任何关于如何修复此代码的想法都将不胜感激。我正在努力寻找员工最多的部门。

表格：

• 部门(DID、dname、managerID(
• 员工(empid、姓名、工资、DID(

代码：

select dname 
from division
where did in (select did from employee group by did having max(count(empid)))

Answer 1

我没有您的表，但Scott的示例模式包含类似的表——dept(部门(和emp(在这些部门工作的员工(。

每个部门有多少员工？

SQL> select deptno, count(*)
2  from emp
3  group by deptno
4  order by 2 desc;
DEPTNO   COUNT(*)
---------- ----------
30          6    --> this is what you want, it has the most employees
20          5
10          3

是哪个部门？

SQL> select * from dept order by deptno;
DEPTNO DNAME          LOC
---------- -------------- -------------
10 ACCOUNTING     NEW YORK
20 RESEARCH       DALLAS
30 SALES          CHICAGO             --> it's SALES
40 OPERATIONS     BOSTON
SQL>

这就是您所尝试的：并不是说它不起作用(Oracle不允许(，而是语法错误。having子句应该类似于having count(*) = 25，而不仅仅是您的愿望实现(having max(count(*))读作"给我拥有最大员工数的部门"(。

SQL> select dname
2  from dept
3  where deptno in (select deptno from emp
4                   group by deptno
5                   having max(count(empno))
6                  );
having max(count(empno))
*
ERROR at line 5:
ORA-00935: group function is nested too deeply

SQL>

那么，我们能做些什么呢？一个简单的选择是根据员工数量对部门进行排名：

SQL> select deptno,
2         count(*) cnt,
3         rank() over (order by count(*) desc) rnk
4  from emp
5  group by deptno;
DEPTNO        CNT        RNK
---------- ---------- ----------
30          6          1      --> department 30 ranks as the highest
20          5          2
10          3          3
SQL>

剩下的很简单：将该查询用作子查询(或CTE，就像我所做的那样(，然后选择排名最高的查询：

SQL> with temp as
2    (select deptno,
3            count(*) cnt,
4            rank() over (order by count(*) desc) rnk
5     from emp
6     group by deptno
7    )
8  select d.dname
9  from dept d join temp t on t.deptno = d.deptno
10  where t.rnk = 1;         --> the highest rank
DNAME
--------------
SALES
SQL>

是销售。