GROUP BY 子句后的 Oracle CONNECT BY 子句
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/10043445/
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
Oracle CONNECT BY clause after GROUP BY clause
提问by Lukas Eder
I have just run across this interesting article here, showing how to simulate wm_concat()or group_concat()in Oracle using a hierarchical query and window functions:
我刚刚在这里看到了这篇有趣的文章，展示了如何使用分层查询和窗口函数在 Oracle 中模拟wm_concat()或group_concat()：
SELECT deptno,
LTRIM(MAX(SYS_CONNECT_BY_PATH(ename,','))
KEEP (DENSE_RANK LAST ORDER BY curr),',') AS employees
FROM (SELECT deptno,
ename,
ROW_NUMBER() OVER (PARTITION BY deptno ORDER BY ename) AS curr,
ROW_NUMBER() OVER (PARTITION BY deptno ORDER BY ename) -1 AS prev
FROM emp)
GROUP BY deptno
CONNECT BY prev = PRIOR curr AND deptno = PRIOR deptno
START WITH curr = 1;
Although, I find this not a very readable solution, it's quite interesting, specifically because the CONNECT BY .. STARTS WITHclause comes afterthe GROUP BYclause. According to the specification, this shouldn't be possible. I've tried this using a simple query and it does work, though! The following two queries return the same results:
虽然，我觉得这不是一个非常可读的解决方案，这是相当有趣的，特别是因为该CONNECT BY .. STARTS WITH条款来后的GROUP BY条款。根据规范，这应该是不可能的。我已经使用一个简单的查询尝试了这个，但它确实有效！以下两个查询返回相同的结果：
-- wrong according to the specification:
select level from dual group by level connect by level <= 2;
-- correct according to the specification:
select level from dual connect by level <= 2 group by level;
Is this an undocumented feature? Or just syntax indifference for convenience? Or do the two statements subtly behave differently?
这是一个未记录的功能吗？或者只是为了方便而对语法漠不关心？或者这两个陈述是否巧妙地表现不同？
采纳答案by Jon Heller
I think this is just an insignificant syntax difference.
我认为这只是一个微不足道的语法差异。
More specifically, I think this is a documentation bug. The syntax diagram for 8i implies that either order is supported. Nothing in the 8i referenceimplies the order makes any difference. But that diagram also kind of implies that you can have multiple group_by_clauseor hierarchical_query, which isn't true:
更具体地说，我认为这是一个文档错误。8i 的语法图暗示支持任一顺序。8i 参考中没有任何内容暗示顺序有任何区别。但是那个图也暗示你可以有多个group_by_clauseor hierarchical_query，这不是真的：
--You can't group twice: ORA-01787: only one clause allowed per query block
select level from dual connect by level <= 2 group by level group by level;
My guess is that when Oracle fixed the syntax diagram for 9i they also forgot the order could be different. Or maybe they intentionally left it out, because it seems more logical to do the hierarchical part first.
我的猜测是，当 Oracle 修复 9i 的语法图时，他们也忘记了顺序可能不同。或者他们可能故意将其排除在外，因为首先进行分层部分似乎更合乎逻辑。
There are several minor syntax variations like this that are undocumented. I don't think it means that they are unsupported. Oracle probably regrets allowing so many weird options and wants things to at least look simple. For example, HAVINGcan come before GROUP BY, many of the old parallel features still work (but are ignored), etc. (This is why I always laugh when people say they are going to quickly "parse SQL" - good luck figuring this out!)
有几个像这样的小的语法变化是未记录的。我不认为这意味着它们不受支持。Oracle 可能对允许这么多奇怪的选项感到遗憾，并希望事情至少看起来很简单。例如，HAVINGcan come before GROUP BY，许多旧的并行功能仍然有效（但被忽略了）等等（这就是为什么当人们说他们要快速“解析 SQL”时我总是笑的原因 - 祝你好运！）
Oracle 8i syntax:
Oracle 8i 语法：
Oracle 9i syntax:
Oracle 9i 语法：
回答by Dave Costa
Look at the execution plans. In my environment they are identical, with a CONNECT BY operation feeding into a HASH GROUP BY. So it appears that placing the GROUP BY first is just an odd syntax that produces the same result as the more natural ordering.
看执行计划。在我的环境中，它们是相同的，将 CONNECT BY 操作输入到 HASH GROUP BY 中。因此，将 GROUP BY 放在第一位似乎只是一种奇怪的语法，它产生与更自然的排序相同的结果。
Technically, this is probably a bug in the parser, since as you say the spec indicates that the hierarchical-query clause should come prior to the group-by clause. But it doesn't appear to make any difference in how the query is executed.
从技术上讲，这可能是解析器中的一个错误，因为正如您所说，规范表明分层查询子句应该在 group-by 子句之前。但它似乎对查询的执行方式没有任何影响。
