oracle SELECT DISTINCT 是否意味着某种结果
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/691562/
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
Does SELECT DISTINCT imply a sort of the results
提问by S Ennis
Does including DISTINCT in a SELECT query imply that the resulting set should be sorted?
在 SELECT 查询中包含 DISTINCT 是否意味着应该对结果集进行排序？
I don't think it does, but I'm looking for a an authoritative answer (web link).
我不认为是这样，但我正在寻找权威答案（网络链接）。
I've got a query like this:
我有一个这样的查询：
Select Distinct foo
From Bar
In oracle, the results are distinct but are not in sorted order. In Jet/MS-Access there seems to be some extra work being done to ensure that the results are sort. I'm assuming that oracle is following the spec in this case and MS Access is going beyond.
在 oracle 中，结果是不同的，但没有排序。在 Jet/MS-Access 中，似乎需要做一些额外的工作来确保结果排序。我假设在这种情况下 oracle 遵循规范并且 MS Access 正在超越。
Also, is there a way I can give the table a hint that it should be sorting on foo(unless otherwise specified)?
另外，有没有办法可以给表格一个提示，它应该排序foo（除非另有说明）？
采纳答案by Chris Shaffer
From the SQL92 specification:
从SQL92 规范：
If DISTINCT is specified, then let TXA be the result of eliminating redundant duplicate values from TX. Otherwise, let TXA be TX.
如果指定了 DISTINCT，则令 TXA 为从 TX 中消除冗余重复值的结果。否则，令 TXA 为 TX。
...
...
4) If an is not specified, then the ordering of the rows of Q is implementation-dependent.
4) 如果没有指定 an，则 Q 的行的顺序取决于实现。
Ultimately the real answer is that DISTINCT and ORDER BY are two separate parts of the SQL statement; If you don't have an ORDER BY clause, the results by definition will not be specifically ordered.
最终真正的答案是 DISTINCT 和 ORDER BY 是 SQL 语句的两个独立部分；如果您没有 ORDER BY 子句，则不会对定义的结果进行特别排序。
回答by David Aldridge
No. There are a number of circumstances in which a DISTINCT in Oracle does not imply a sort, the most important of which is the hashing algorithm used in 10g+ for both group by and distinct operations.
不可以。在很多情况下，Oracle 中的 DISTINCT 并不意味着排序，其中最重要的是 10g+ 中用于 group by 和 distinct 操作的散列算法。
Alwaysspecify ORDER BY if you want an ordered result set, even in 9i and below.
如果您想要有序的结果集，请始终指定 ORDER BY，即使在 9i 及以下版本中也是如此。
回答by dwc
There is no "authoritative" answer link, since this is something that no SQL server guarantees.
没有“权威”答案链接，因为这是 SQL 服务器无法保证的。
You will often see results in order when using distinct as a side effect of the best methods of finding those results. However, any number of other things can mix up the results, and some server may hand back results in such a way as to not give them sorted even if it had to sort to get the results.
当使用不同作为找到这些结果的最佳方法的副作用时，您通常会按顺序看到结果。但是，任何数量的其他事情都可能混淆结果，并且某些服务器可能会以不给结果排序的方式返回结果，即使它必须排序才能获得结果。
Bottom line: if your server doesn't guarantee something you shouldn't count on it.
底线：如果您的服务器不能保证某些东西，您不应该指望它。
回答by jerebear
No, it is not implying a sort. In my experience, it sorts by the known index, which may happen to be foo.
不，这并不意味着某种排序。根据我的经验，它按已知索引排序，这可能恰好是 foo。
Why be subtle? Why not specific Select Distinct foo from Bar Order by foo?
为什么要微妙？为什么不从 Bar Order by foo 中选择特定的 Select Distinct foo？
回答by marc_s
Not to my knowledge, no. The only reason I can think of is that SQL Server would internally sort the data in order to detect and filter out duplicates, and thus return it in a "pre-sorted" manner. But I wouldn't rely on that "side effect" :-)
据我所知，不。我能想到的唯一原因是 SQL Server 会在内部对数据进行排序以检测和过滤掉重复项，从而以“预先排序”的方式返回它。但我不会依赖那个“副作用”:-)
回答by Dan Breslau
On at least one server I've used (probably either Oracle or SQL Server, about six years ago), SELECT DISTINCT was rejected if you didn't have an ORDER BY clause. It was accepted on the "other" server (Oracle or SQL Server). Your mileage may vary.
在我使用过的至少一台服务器上（大约六年前，可能是 Oracle 或 SQL Server），如果您没有 ORDER BY 子句，则 SELECT DISTINCT 被拒绝。它在“其他”服务器（Oracle 或 SQL Server）上被接受。你的旅费可能会改变。
回答by Stephen Pace
No, the results are not sorted. If you want to give it a 'hint', you can certainly supply an ORDER BY:
不，结果未排序。如果你想给它一个“提示”，你当然可以提供一个 ORDER BY：
select distinct foo from bar order by foo
从 bar order by foo 中选择不同的 foo
But keep in mind that you might want to sort on more than just alphabetically. Instead you might want to sort on criteria on other fields. See:
但请记住，您可能想要的不仅仅是按字母顺序排序。相反，您可能希望根据其他字段的条件进行排序。看：
http://weblogs.sqlteam.com/jeffs/archive/2007/12/13/select-distinct-order-by-error.aspx
http://weblogs.sqlteam.com/jeffs/archive/2007/12/13/select-distinct-order-by-error.aspx
回答by Jonathan Leffler
As the answers mostly say, DISTINCT does not mandate a sort - only ORDER BY mandates that. However, one standard way of achieving DISTINCT results is to sort; the other is to hash the values (which tends to lead to semi-random sequencing). Relying on the sort effect of DISTINCT would be foolish.
正如大多数答案所说， DISTINCT 不强制要求排序 - 只有 ORDER BY 强制要求。但是，实现 DISTINCT 结果的一种标准方法是排序；另一种是散列值（这往往导致半随机排序）。依靠 DISTINCT 的排序效果是愚蠢的。
回答by Nitin Rajwar
In my case (SQL server), as an example I had a list of countries with a numerical value X assigned against each. When I did a select distinct * from Table order by X, it ordered it by X but at the same time result set countries were also ordered which was not directly implemented. From my experience, I'll say that distinct does imply an implicit sort.
在我的例子中（SQL 服务器），作为一个例子，我有一个国家列表，每个国家都分配了一个数值 X。当我按 X 从 Table order 中选择不同的 * 时，它按 X 对其进行排序，但同时结果集国家/地区也被排序，但并未直接实现。根据我的经验，我会说 distinct 确实意味着隐式排序。
回答by EvilTeach
Yes. Oracle does use a sort do calculate a distinct. You can see that if you look at the explain plan. The fact that it did a sort for that calculation does not in any way imply that the result set will be sorted. If you want the result set sorted, you are required to use the ORDER BY clause.
是的。Oracle 确实使用排序来计算不同。如果您查看解释计划，您就可以看到这一点。它对该计算进行排序这一事实绝不意味着结果集将被排序。如果要对结果集进行排序，则需要使用 ORDER BY 子句。
