按不在 Oracle 子查询中工作的顺序
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/11882214/
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
Order by not working in Oracle subquery
提问by Nick
I'm trying to return 7 events from a table, from todays date, and have them in date order:
我试图从表中返回 7 个事件，从今天的日期开始，并按日期顺序排列它们：
SELECT ID
FROM table
where ID in (select ID from table
where DATEFIELD >= trunc(sysdate)
order by DATEFIELD ASC)
and rownum <= 7
If I remove the 'order by' it returns the IDs just fine and the query works, but it's not in the right order. Would appreciate any help with this since I can't seem to figure out what I'm doing wrong!
如果我删除“order by”，它会返回 ID 就好了并且查询有效，但它的顺序不正确。感谢您对此的任何帮助，因为我似乎无法弄清楚我做错了什么！
(edit) for clarification, I was using this before, and the order returned was really out:
（编辑）为了澄清，我之前使用过这个，返回的订单真的出来了：
select ID
from TABLE
where DATEFIELD >= trunc(sysdate)
and rownum <= 7
order by DATEFIELD
Thanks
谢谢
回答by a_horse_with_no_name
The values for the ROWNUM"function" are applied beforethe ORDER BY is processed. That why it doesn't work the way you used it (See the manualfor a similar explanation)
在处理 ORDER BY之前ROWNUM应用“函数”的值。这就是为什么它不能按照您使用的方式工作（有关类似解释，请参阅手册）
When limiting a query using ROWNUMand an ORDER BY is involved, the ordering must be done in an inner select and the limit must be applied in the outer select:
当限制查询 usingROWNUM并且涉及 ORDER BY 时，必须在内部选择中完成排序，并且必须在外部选择中应用限制：
select *
from (
select *
from table
where datefield >= trunc(sysdate)
order by datefield ASC
)
where rownum <= 7
回答by Tomek Szpakowicz
You cannot use
order byinwhere id in (select id from ...)kind of subquery. It wouldn't make sense anyway. This condition only checks ifidis in subquery. If it affects the order of output, it's only incidental. With different data query execution plan might be different and output order would be different as well. Use explicitorder byat the end of the main query.It is well known 'feature' of Oracle that
rownumdoesn't play nice withorder by. See http://www.adp-gmbh.ch/ora/sql/examples/first_rows.htmlfor more information. In your case you should use something like:SELECT ID FROM (select ID, row_number() over (order by DATEFIELD ) r from table where DATEFIELD >= trunc(sysdate)) WHERE r <= 7
您不能
order by在where id in (select id from ...)类型的子查询中使用。反正也没意思。此条件仅检查是否id在子查询中。如果它影响输出的顺序，那只是偶然的。不同的数据查询执行计划可能不同，输出顺序也会不同。order by在主查询的末尾使用显式。Oracle 众所周知的“特性”
rownum与order by. 有关更多信息，请参阅http://www.adp-gmbh.ch/ora/sql/examples/first_rows.html。在您的情况下，您应该使用以下内容：SELECT ID FROM (select ID, row_number() over (order by DATEFIELD ) r from table where DATEFIELD >= trunc(sysdate)) WHERE r <= 7
See also:
也可以看看：
- http://www.orafaq.com/faq/how_does_one_select_the_top_n_rows_from_a_table
- http://www.oracle.com/technetwork/issue-archive/2006/06-sep/o56asktom-086197.html
- http://asktom.oracle.com/pls/asktom/f?p=100:11:507524690399301::::P11_QUESTION_ID:127412348064
- http://www.orafaq.com/faq/how_does_one_select_the_top_n_rows_from_a_table
- http://www.oracle.com/technetwork/issue-archive/2006/06-sep/o56asktom-086197.html
- http://asktom.oracle.com/pls/asktom/f?p=100:11:507524690399301::::P11_QUESTION_ID:127412348064
See also other similar questions on SO, eg.:
另请参阅有关 SO 的其他类似问题，例如：
回答by Diego
Your outer query cant "see" the ORDER in the inner query and in this case the order in the inner doesn't make sense because it (the inner) is only being used to create a subset of data that will be used on the WHERE of the outer one, so the order of this subset doesn't matter.
您的外部查询无法“看到”内部查询中的 ORDER，在这种情况下，内部的订单没有意义，因为它（内部）仅用于创建将在 WHERE 上使用的数据子集的，所以这个子集的顺序无关紧要。
maybe if you explain better what you want to do, we can help you
也许如果你更好地解释你想做什么，我们可以帮助你
回答by Moreno
ORDER BY CLAUSE IN Subqueries: the order by clause is not allowed inside a subquery, with the exception of the inline views. If attempt to include an ORDER BY clause, you receive an error message
ORDER BY CLAUSE IN Subqueries：子查询中不允许使用 order by 子句，内联视图除外。如果尝试包含 ORDER BY 子句，您会收到一条错误消息
An inline View is a query at the from clause.
内联视图是对 from 子句的查询。
SELECT t.*
FROM (SELECT id, name FROM student) t
SELECT t.*
FROM (SELECT id, name FROM student) t
