oracle 内部查询中的 Hibernate 和 rownum
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2330367/
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
Hibernate and rownum in inner query
提问by mransley
I have the following query in oracle that I want to use in hibernate but cannot work out how to use the rownum variable in an inner query. The query is as follows:
我在 oracle 中有以下查询，我想在 hibernate 中使用它，但无法弄清楚如何在内部查询中使用 rownum 变量。查询如下：
select emp.prof_key, emp.prof_display_name
from empinst.emp emp
where emp.prof_key IN (select x.object_key
from (select event.object_key as object_key
from empinst.eventlog event
where event.event_name = 'profiles.created'
and event.event_source = 'Profiles'
order by event.created desc) x
where rownum <= 10)
The only way that I can work out how to do it is to break the query into two parts but I assume there must be a more efficient way to do it in hibernate.
我可以弄清楚如何做到这一点的唯一方法是将查询分成两部分，但我认为必须有一种更有效的方法在休眠中进行。
Thanks in advance.
提前致谢。
Michael.
迈克尔。
回答by Vincent Malgrat
your query will not filter the 10 most recent records. It will return 10 randomrecords (Random as in unreliable order) since there is no ORDER BY clause.
您的查询不会过滤最近的 10 条记录。由于没有 ORDER BY 子句，它将返回 10 个随机记录（随机顺序不可靠）。
I'm not familiar with the limitations of hibernate but as with all limiting tools I'm pretty sure you can work around them with a well-thought view.
我不熟悉 hibernate 的限制，但与所有限制工具一样，我很确定您可以通过深思熟虑的观点解决这些问题。
For example, this view will contain a rank column you can use to filter the 10 most recent record for any combination of (event_name, event_source)based on an ordering column event_date:
例如，此视图将包含一个排名列，您可以使用它来筛选(event_name, event_source)基于排序列的任意组合的 10 个最新记录event_date：
CREATE VIEW eventlog_rank_v AS
SELECT e.*,
row_number() OVER (PARTYTION BY e.event_name,
e.event_source
ORDER BY e.event_date DESC) event_rank
FROM empinst.eventlog e;
