如何在没有 ORDER BY 的情况下对 UNION 中的条目进行排序？
如何确定我的结果集首先是 a，然后是 b？ 它将帮助我解决棘手的订购问题。
这是我正在做的事情的一个简化示例：
SELECT a FROM A LIMIT 1
UNION
SELECT b FROM B LIMIT 1;
How can I be sure that my result set will have a first and b second? It would help me to solve a tricky ordering problem.
Here is a simplified example of what I'm doing:
SELECT a FROM A LIMIT 1
UNION
SELECT b FROM B LIMIT 1;
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（7）
我不认为顺序是有保证的，至少在所有 DBMS 中是这样。
我过去为控制 UNION 中的排序所做的是：
I don't think order is guaranteed, at least not across all DBMS.
What I've done in the past to control the ordering in UNIONs is:
使用
UNION的结果集将消除不同的值。我在文档中找不到任何证据，但从
10年的经验来看，我可以看出UNION ALL确实保留了顺序，至少在Oracle中是这样。但是，如果您正在建造核电站或类似的设施，请不要依赖于此。
Your result set with
UNIONwill eliminate distinct values.I can't find any proof in documentation, but from
10years experience I can tell thatUNION ALLdoes preserve order, at least inOracle.Do not rely on this, however, if you're building a nuclear plant or something like that.
不，SQL 查询中的结果顺序仅由 ORDER BY 子句控制。 在某些情况下，您可能碰巧看到没有 ORDER BY 子句的有序结果，但这是偶然的（例如优化器当前查询计划的副作用）并且不能保证。
棘手的排序问题是什么？
No, the order of results in a SQL query is controlled only by the ORDER BY clause. It may be that you happen to see ordered results without an ORDER BY clause in some situation, but that is by chance (e.g. a side-effect of the optimiser's current query plan) and not guaranteed.
What is the tricky ordering problem?
我知道对于 Oracle 来说，没有办法保证哪个在没有 order by 的情况下会先出现。 问题是，如果您尝试它，即使您运行它的大多数时间，它也可能以正确的顺序出现。 但一旦你在生产中依赖它，就会出现错误。
I know for Oracle there is no way to guarantee which will come out first without an order by. The problem is if you try it it may come out in the correct order even for most of the times you run it. But as soon as you rely on it in production, it will come out wrong.
我本以为不会，因为数据库很可能需要执行 ORDER BY 才能进行 UNION。
UNION ALL 的行为可能有所不同，但是 YMMV。
I would have thought not, since the database would most likely need to do an ORDER BY in order to the UNION.
UNION ALL might behave differently, but YMMV.
简短的回答是肯定的，你会先得到 A，然后得到 B。
The short answer is yes, you will get A then B.