Oracle SQL - 在 Have 子句中使用 ROWNUM 分组？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/40443917/
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
Oracle SQL - Group by with ROWNUM in Having clause?
提问by lte__
I'm trying to get the user in the database who has the ownership over the biggest segment in the database. For this I'm trying:
我试图让数据库中的用户拥有数据库中最大段的所有权。为此，我正在尝试：
SELECT owner, MAX(bytes)
FROM SYS.DBA_SEGMENTS
GROUP BY owner
HAVING ROWNUM <= 1;
This, however, returns "not a GROUP BY expression". Why can't I select the first row only? How can I write this query? Thank you!
然而，这会返回"not a GROUP BY expression"。为什么我不能只选择第一行？我该如何编写此查询？谢谢！
回答by Gordon Linoff
You can. In Oracle 12c+, you can do:
你可以。在 Oracle 12c+ 中，您可以执行以下操作：
SELECT owner, MAX(bytes)
FROM SYS.DBA_SEGMENTS
GROUP BY owner
ORDER BY MAX(bytes) DESC
FETCH FIRST ROW ONLY;
Note the ORDER BY.
注意ORDER BY.
In earlier versions you need a subquery:
在早期版本中，您需要一个子查询：
SELECT o.*
FROM (SELECT owner, MAX(bytes)
FROM SYS.DBA_SEGMENTS
GROUP BY owner
ORDER BY MAX(bytes) DESC
) o
WHERE rownum = 1;
回答by mathguy
In earlier versions, you can also use (just one pass over the data):
在早期版本中，您还可以使用（只需传递数据一次）：
select max(owner) keep (dense_rank last order by bytes nulls first) as owner,
max(bytes) as bytes
from sys.dba_segments;
