甲骨文 12c - "not a single-group group function"

Question

我有一个表格 o 员工，其中包含姓名、就业日期和更多信息。

我想检查哪一年雇用的员工最多。

我写了一个查询，计算每年的就业情况：

SELECT EXTRACT (YEAR FROM e1.empl_date) AS YEAR, COUNT(e1.id_empl) AS EMPL_NUMBER
FROM employees e1
GROUP BY EXTRACT (YEAR FROM e1.empl_date);

此查询的结果是元组：

YEAR   | EMPL_NUMBER
1993   | 3
1997   | 2

等等...

现在我想得到最大的EMPL_NUMBER：

SELECT YEAR, MAX(EMPL_NUMBER)
FROM (SELECT EXTRACT (YEAR FROM e1.empl_date) AS YEAR, COUNT(e1.id_empl) AS EMPL_NUMBER
FROM employees e1
GROUP BY EXTRACT (YEAR FROM e1.empl_date));

然后我收到一个错误：

ORA-00937: not a single-group group function

我不明白为什么我会收到错误，因为子查询返回包含 2 列的元组。

Answer 1

您正在对选择结果使用聚合函数，因此如果您需要所有不同的年份，则

按

SELECT T.YEAR, MAX(T.EMPL_NUMBER)
FROM (
SELECT EXTRACT (YEAR FROM e1.empl_date) AS YEAR, COUNT(e1.id_empl) AS EMPL_NUMBER
FROM employees e1
GROUP BY EXTRACT (YEAR FROM e1.empl_date)
) T 
GROUP BY T.YEAR ;

否则，如果您需要MAX(EMPL_NUMBER)的年份，您可以

SELECT T.YEAR, T.EMPL_NUMBER
FROM  (
SELECT EXTRACT (YEAR FROM e1.empl_date) AS YEAR, COUNT(e1.id_empl) AS EMPL_NUMBER
FROM employees e1
GROUP BY EXTRACT (YEAR FROM e1.empl_date)
) T
WHERE (T.EMPL_NUMBER)  IN (SELECT  MAX(EMPL_NUMBER)
FROM (
SELECT EXTRACT (YEAR FROM e1.empl_date) AS YEAR, COUNT(e1.id_empl) AS EMPL_NUMBER
FROM employees e1
GROUP BY EXTRACT (YEAR FROM e1.empl_date)
) T1  )

Answer 2

在 Oracle 12C 中，您可以执行以下操作：

SELECT EXTRACT(YEAR FROM e1.empl_date) AS YEAR, COUNT(e1.id_empl) AS EMPL_NUMBER
FROM employees e1
GROUP BY EXTRACT(YEAR FROM e1.empl_date)
ORDER BY COUNT(e1.id_empl) DESC
FETCH FIRST 1 ROW ONLY;

Answer 3

执行此操作的一种方法是像您已经执行的那样使用聚合查询，然后充分利用聚合函数。例如，使用FIRST/LAST函数(并使用SCOTT架构，EMP表进行说明)：

select min(extract(year from hiredate)) keep (dense_rank last order by count(empno)) as yr,
max(count(empno)) as emp_count
from   emp
group by extract(year from hiredate)
;

YR  EMP_COUNT
----  ---------
1981         10

此解决方案存在两个问题。首先，许多开发人员(包括许多有经验的开发人员)似乎不知道 FIRST/LAST 函数，或者不愿意使用它。另一个更严重的问题是，在这个问题上，可能有几年的雇用人数相同，最高。问题要求必须比原始帖子更详细。当第一名并列时，期望的输出是多少？

上面的查询返回实现最大雇用人数的所有不同年份中的最早年份。将SELECT条款中的MIN更改为MAX，您将获得最近一年雇用人数最多的年份。但是，我们通常想要一个查询，在领带的情况下，将返回大多数雇员的所有年份。这不能用FIRST/LAST函数来完成。

为此，紧凑的解决方案将在原始查询中添加分析函数，以按雇用人数对年份进行排名。然后在外部查询中，只需筛选排名 = 1 的行。

select yr, emp_count
from   (
select   extract(year from hiredate) as yr, count(empno) as emp_count,
rank() over (order by count(empno) desc) as rnk
from     emp
group by extract(year from hiredate)
)
where  rnk = 1
;

或者，在子查询的SELECT子句中使用max()分析函数(而不是秩类型分析函数)：

select yr, emp_count
from   (
select   extract(year from hiredate) as yr, count(empno) as emp_count,
max(count(empno)) over () as max_count
from     emp
group by extract(year from hiredate)
)
where  emp_count = max_count
;

Answer 4

我假设你想要一行显示大多数人被雇用的年份：

SELECT * FROM (
SELECT EXTRACT (YEAR FROM e1.empl_date) AS YEAR, 
COUNT(e1.id_empl) AS EMPL_NUMBER
FROM employees e1
GROUP BY EXTRACT (YEAR FROM e1.empl_date)
ORDER BY COUNT(*))
WHERE ROWNUM=1;