DISTINCT如何与ORDER BY互动? [英] How does DISTINCT interact with ORDER BY?

问题描述

请考虑以下两个表:

user:
ID | name
---+--------
1  | Alice
2  | Bob
3  | Charlie

event:
order | user
------+------------
1     | 1 (Alice)
2     | 2 (Bob)
3     | 3 (Charlie)
4     | 3 (Charlie)
5     | 2 (Bob)
6     | 1 (Alice)

如果我运行以下查询:

SELECT DISTINCT user FROM event ORDER BY "order" DESC;

是否可以保证我按以下顺序获得结果?

will it be guaranteed that I get the results in the following order?

1 (Alice)
2 (Bob)
3 (Charlie)

如果选择了event的最后三行，则我知道这是我得到的顺序，因为它将按降序对4、5、6进行排序.但是，如果选择了前三行，然后DISTINCT阻止加载最后一棵树供考虑，我将以相反的顺序得到它.

If the three last rows of event are selected, I know this is the order I get, because it would be ordering 4, 5, 6 in descending order. But if the first three rows are selected, and then DISTINCT prevents the last tree to be loaded for consideration, I would get it in reversed order.

此行为在SQL中是否定义正确?两者中的哪一个会发生?在SQLite中呢?

Is this behavior well defined in SQL? Which of the two will happen? What about in SQLite?

推荐答案

不，不能保证.
查找Itzik Ben-Gan的MS SQL逻辑查询处理阶段海报.它会迁移到许多站点上，这些站点当前位于 https: //accessexperts.com/wp-content/uploads/2015/07/Logical-Query-Processing-Poster.pdf . DISTINCT优先于ORDER BY .. TOP，并且Sql Server可以自由为Alice返回任何1 | 1 (Alice)或6 | 1 (Alice)行.因此(1,2,3)，(1,4,5)等中的任何一个都是DISTINCT的有效结果.

No, it will not be guaranteed.
Find Itzik Ben-Gan's Logical Query Processing Phases poster for MS SQL. It migrates over many sites, currently found at https://accessexperts.com/wp-content/uploads/2015/07/Logical-Query-Processing-Poster.pdf . DISTINCT preceeds ORDER BY .. TOP and Sql Server is free to return any of 1 | 1 (Alice) or 6 | 1 (Alice) rows for Alice. So any of (1,2,3), (1,4,5) an so on are valid results of DISTINCT.

这篇关于DISTINCT如何与ORDER BY互动?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！