blocks|key|740458|text|是的:不应该这样做，因为它在概念上没有意义。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|740459|子查询将在某些外部查询中使用(否则将毫无意义)，且外部查询无论如何都必须进行排序，因此对子查询进行排序没有意义。|740460|这是因为SQL中的查询结果没有特定的顺序，除非您使用显式顺序。因此，即使在子查询中使用了ORDER，也不能保证这会影响外部查询结果的顺序；因此这是没有意义的。|740461|当然，由于其实现，它可能会对某些特定RDBMS产生影响，但这将是特定于实现的，而不是您应该依赖的东西。|740462|Edit:当然，如果在子查询中使用TOP或LIMIT，则需要使用ORDER。但这不是标准的SQL+...|740463|entityMap^0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|L|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|M|8|@]|9|@]|A|$]]|$1|D|3|E|5|6|7|N|8|@]|9|@]|A|$]]|$1|F|3|G|5|6|7|O|8|@]|9|@]|A|$]]|$1|H|3|I|5|6|7|P|8|@]|9|@]|A|$]]|$1|J|3|-4|5|6|7|Q|8|@]|9|@]|A|$]]]|K|$]]

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.

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.

Edit: Of course, if you use TOP or LIMIT in the subquery, you will need to use ORDER. But that's not standard SQL anyway...

blocks|key|948010|text|如果子查询使用某种类型的LIMIT+/+TOP，则应该使用它。|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|948011|除非子查询也包含TOP或FOR+XML子句，否则SQL+Server不允许这样做：|948012|--+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|code-block|syntax|javascript|948013|entityMap^0|C|5|K|3|0|8|3|C|7|O|A|0|0^^$0|@$1|2|3|4|5|6|7|O|8|@$9|P|A|Q|B|C]|$9|R|A|S|B|C]]|D|@]|E|$]]|$1|F|3|G|5|6|7|T|8|@$9|U|A|V|B|C]|$9|W|A|X|B|C]|$9|Y|A|Z|B|C]]|D|@]|E|$]]|$1|H|3|I|5|J|7|10|8|@]|D|@]|E|$K|L]]|$1|M|3|-4|5|6|7|11|8|@]|D|@]|E|$]]]|N|$]]

You should use it if the subquery uses some kind of <code>LIMIT</code> / <code>TOP</code>.

<code>SQL Server</code> will not allow it unless the subquery contains <code>TOP</code> or <code>FOR XML</code> clause as well:

<pre><code>-- 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
</code></pre>

blocks|key|2040919|text|除非使用top，否则它没有什么用处，因为无论如何都要在外部查询中进行排序|type|unstyled|depth|inlineStyleRanges|entityRanges|data|2040920|entityMap^0|0^^$0|@$1|2|3|4|5|6|7|D|8|@]|9|@]|A|$]]|$1|B|3|-4|5|6|7|E|8|@]|9|@]|A|$]]]|C|$]]

Unless you use top it is not useful since you will be ordering in the outer query anyway

blocks|key|740021|text|更聪明的人说，这不是正确/有效的方法。在我的例子中，SELECT+TOP+100%25+In+sub-query解决了这个问题。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|740022|干杯|740023|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|F|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|G|8|@]|9|@]|A|$]]|$1|D|3|-4|5|6|7|H|8|@]|9|@]|A|$]]]|E|$]]

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.

Cheers

blocks|key|948054|text|你可以做到，但我通常不会，除非你有需要。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|948055|优化器将忽略它(或者抛出错误？)|948056|见"Intermediate+materialisation“一词中的一些用法。|offset|length|948057|entityMap|0|LINK|mutability|MUTABLE|url|http://dataeducation.com/cursors-run-just-fine/^0|0|0|2|S|0|0^^$0|@$1|2|3|4|5|6|7|P|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|Q|8|@]|9|@]|A|$]]|$1|D|3|E|5|6|7|R|8|@]|9|@$F|S|G|T|1|U]]|A|$]]|$1|H|3|-4|5|6|7|V|8|@]|9|@]|A|$]]]|I|$J|$5|K|L|M|A|$N|O]]]]

You can do it, but I wouldn't usually unless you have a need.

The optimiser will ignore it (or throw an error?)

See "<a href="http://dataeducation.com/cursors-run-just-fine/" rel="nofollow noreferrer">Intermediate materialisation</a>" for some usages.

blocks|key|739914|text|根据子查询的大小，它将在不同程度上影响性能。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|739915|不过，对子查询来说，顺序不应该很重要。您应该能够将Order+By部分移动到外部查询(应该是返回最终结果的查询)。|739916|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|F|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|G|8|@]|9|@]|A|$]]|$1|D|3|-4|5|6|7|H|8|@]|9|@]|A|$]]]|E|$]]

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).

blocks|key|948256|text|当您对整体数据的一个子集感兴趣时，任何ORDER+BY在子查询中都是无效的，因此您总是需要一个TOP+(SQL+Server)。子查询中没有TOP的ORDER+BY是没有意义的，因为结果的整体排序是由外部查询处理的。|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|948257|entityMap^0|1B|3|0^^$0|@$1|2|3|4|5|6|7|H|8|@$9|I|A|J|B|C]]|D|@]|E|$]]|$1|F|3|-4|5|6|7|K|8|@]|D|@]|E|$]]]|G|$]]

No ORDER BY is valid in a subquery when you are interested in a subset of the overall data, hence you always need a <code>TOP</code> (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.

blocks|key|740004|text|您不应该使用它。根据“SQL的艺术”，这实际上阻止了优化器执行各种优化，因为这会过早地转换数据。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|740005|entityMap^0|0^^$0|@$1|2|3|4|5|6|7|D|8|@]|9|@]|A|$]]|$1|B|3|-4|5|6|7|E|8|@]|9|@]|A|$]]]|C|$]]

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.

blocks|key|2357005|text|这是完全合法的。即SELECT+id+FROM+entries+WHERE+author_id+IN+(SELECT+id+FROM+authors+ORDER+BY+name+DESC)，但你通常会得到相同的结果。|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|2357006|entityMap^0|9|2D|0^^$0|@$1|2|3|4|5|6|7|H|8|@$9|I|A|J|B|C]]|D|@]|E|$]]|$1|F|3|-4|5|6|7|K|8|@]|D|@]|E|$]]]|G|$]]

It's totally legit. I.e. <code>SELECT id FROM entries WHERE author_id IN (SELECT id FROM authors ORDER BY name DESC)</code> but you'll really get the same results usually.

Is there any reason why or why not you should do an 'order by' in a subquery?

Is order by clause allowed in a subquery

翻译质量差，导致语言生硬或混乱。

没有提供实际的解决方法或示例。

解答不清晰，无法理解或解决问题。

页面排版不美观，阅读体验差。

文章

问答

视频

学习中心

腾讯云实验室

直播

竞赛

腾讯云架构师技术同盟交流圈

腾讯云数据库专区

腾讯云顾问专区

腾讯云原生专区

腾讯混元专区

腾讯云TCE专区

腾讯云Lighthouse专区

腾讯云HAI专区

腾讯云Edgeone专区

腾讯云存储专区

腾讯云智能专区

腾讯轻联专区 

腾讯云开发专区

TAPD专区

腾讯轻量云游戏服专区

腾讯云最具价值专家

腾讯云架构师技术同盟

腾讯云创作之星

腾讯云开发者先锋 

腾讯云代码助手

CODING DevOps

Cloud Studio

SDK中心

API中心

命令行工具

有什么理由或者为什么不应该在子查询中执行“order by”吗？

问子查询中是否允许order by子句
EN

回答 9

Stack Overflow用户

Stack Overflow用户

Stack Overflow用户

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问子查询中是否允许order by子句EN

回答 9

Stack Overflow用户

Stack Overflow用户

Stack Overflow用户

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问子查询中是否允许order by子句
EN