SQL 子查询中是否允许 order by 子句
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2101908/
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
Is order by clause allowed in a subquery
提问by Prady
Is there any reason why or why not you should do an 'order by' in a subquery?
有什么理由为什么或为什么不应该在子查询中执行“order by”？
回答by sleske
Yes: It should not be done, because it does not make sense conceptually.
是的：不应该这样做，因为它在概念上没有意义。
The subquery will be used in some outer query (otherwise it would be pointless), and that outer query will have to do ordering anyway, so there's no point ordering the subquery.
子查询将用于某些外部查询（否则它将毫无意义），并且该外部查询无论如何都必须进行排序，因此对子查询进行排序是没有意义的。
This is because query results in SQL will come in no particular order, unless you use an explicit ORDER. So even if you used ORDER in the subquery, you have no guarantee that this will affect the order of the results from the outer query; so it's pointless.
这是因为 SQL 中的查询结果不会以特定顺序出现，除非您使用显式 ORDER。因此，即使您在子查询中使用了 ORDER，也不能保证这会影响外部查询的结果顺序；所以毫无意义。
It may of course make a difference in some specific RDBMS because of its implementation, but that will be implementation-specific, and not something you should rely on.
由于它的实现，它当然可能会在某些特定的 RDBMS 中产生差异，但这将是特定于实现的，而不是您应该依赖的东西。
Edit:Of course, if you use TOP or LIMIT in the subquery, you willneed to use ORDER. But that's not standard SQL anyway...
编辑：当然，如果在子查询中使用 TOP 或 LIMIT，则需要使用 ORDER。但这无论如何都不是标准的 SQL...
回答by Quassnoi
You should use it if the subquery uses some kind of LIMIT/ TOP.
如果子查询使用某种LIMIT/ ，您应该使用它TOP。
SQL Serverwill not allow it unless the subquery contains TOPor FOR XMLclause as well:
SQL Server除非子查询也包含TOPorFOR XML子句，否则不允许这样做：
-- Fails
WITH q(id) AS
(
SELECT 1
UNION ALL
SELECT 2
)
SELECT *
FROM (
SELECT *
FROM q
ORDER BY
id DESC
) q2
-- Succeeds
WITH q(id) AS
(
SELECT 1
UNION ALL
SELECT 2
)
SELECT *
FROM (
SELECT TOP 1 *
FROM q
ORDER BY
id DESC
) q2
-- Succeeds, but ORDER BY is ignored
WITH q(id) AS
(
SELECT 1
UNION ALL
SELECT 2
)
SELECT *
FROM (
SELECT TOP 100 PERCENT *
FROM q
ORDER BY
id DESC
) q2
回答by SQLMenace
Unless you use top it is not useful since you will be ordering in the outer query anyway
除非您使用 top 否则它没有用，因为您无论如何都会在外部查询中进行排序
回答by Marko
Smarter people say that is not proper/valid way to do it. In my case SELECT TOP 100 PERCENT in sub-query solved the problem.
更聪明的人说这不是正确/有效的方法。在我的情况下，子查询中的 SELECT TOP 100 PERCENT 解决了这个问题。
Cheers
干杯
回答by AdaTheDev
No ORDER BY is valid in a subquery when you are interested in a subset of the overall data, hence you always need a TOP(SQL Server). There's no point having an ORDER BY without TOP in a subquery because the overall ordering of the results is handled by the outer query.
当您对整个数据的一个子集感兴趣时，在子查询中没有 ORDER BY 是有效的，因此您总是需要一个TOP(SQL Server)。在子查询中使用没有 TOP 的 ORDER BY 是没有意义的，因为结果的整体排序是由外部查询处理的。
回答by Jordan Parmer
You should not use it. According to the 'Art of SQL', this actually prevents the optimizer from performing various optimizations that it could otherwise do because this pre-maturely transforms the data.
你不应该使用它。根据“SQL 的艺术”，这实际上阻止了优化器执行它本来可以执行的各种优化，因为这会过早地转换数据。
回答by gbn
You can do it, but I wouldn't usually unless you have a need.
你可以这样做，但我通常不会，除非你有需要。
The optimiser will ignore it (or throw an error?)
优化器将忽略它（或抛出错误？）
See "Intermediate materialisation" for some usages.
有关某些用法，请参阅“中间物化”。
回答by Justin Niessner
Depending on the size of the sub-query, it will impact performance to a varrying degree.
根据子查询的大小，它会在不同程度上影响性能。
Order shouldn't matter on a sub-query though. You should be able to move the Order By portion to the Outer Query (which should be the one returning the final results).
但是，子查询的顺序应该无关紧要。您应该能够将 Order By 部分移动到外部查询（应该是返回最终结果的查询）。
回答by loginx
It's totally legit. I.e. SELECT id FROM entries WHERE author_id IN (SELECT id FROM authors ORDER BY name DESC)but you'll really get the same results usually.
这是完全合法的。即SELECT id FROM entries WHERE author_id IN (SELECT id FROM authors ORDER BY name DESC)但通常你真的会得到相同的结果。
