Oracle SQL - 在Having子句中使用ROWNUM的Group by？

Question

我试图从数据库中获取拥有最大段落所有权的用户。为此，我正在尝试：

SELECT owner, MAX(bytes) 
FROM SYS.DBA_SEGMENTS
GROUP BY owner
HAVING ROWNUM <= 1;

然而，这会返回"not a GROUP BY expression"。为什么我不能只选择第一行？我该如何编写此查询？谢谢！

- lte__

---

ROWNUM 被分配给行来源（在这种情况下是基表 DBA_SEGMENTS 中的各个行）。HAVING 指的是由 GROUP BY 创建的组的属性，而不是指个别行。你不能在 HAVING 中使用 ROWNUM，就像你不能使用 BYTES 或任何可能对单个组内的行具有不同值的其他表达式一样。正如错误消息所说的那样。 - user5683823

Answer 1

您可以在Oracle 12c+中使用以下方式实现：

SELECT owner, MAX(bytes) 
FROM SYS.DBA_SEGMENTS
GROUP BY owner
ORDER BY MAX(bytes) DESC
FETCH FIRST ROW ONLY;

请注意ORDER BY。

在早期版本中，您需要使用子查询：

SELECT o.*
FROM (SELECT owner, MAX(bytes) 
      FROM SYS.DBA_SEGMENTS
      GROUP BY owner
      ORDER BY MAX(bytes) DESC
     ) o
WHERE rownum = 1;

- Gordon Linoff

---

谢谢，早期版本可以使用。你知道为什么 HAVING ROWNUM <= 1; 不起作用吗？ - lte__

我只在SELECT和WHERE子句中使用过rownum。它是一个伪列，由Oracle自己管理，因此语义有点奇怪并不令人惊讶。文档没有提到having子句：https://docs.oracle.com/cd/B19306_01/server.102/b14200/pseudocolumns009.htm。 - Gordon Linoff

Answer 2

在早期版本中，您也可以使用（仅对数据进行一次处理）：

select max(owner) keep (dense_rank last order by bytes nulls first) as owner,
       max(bytes) as bytes
from   sys.dba_segments;

- user5683823