Oracle中GROUP BY子句后的CONNECT BY子句

Question

我刚刚看到了这篇有趣的文章,它展示了如何使用分层查询和窗口函数在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;

虽然我觉得这不是一个非常易读的解决方案，但它相当有趣，特别是因为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;

这是一个未记录的特性吗？还是为了方便而采用的语法不同？或者这两个语句微妙地表现出不同的行为？

- Lukas Eder

Answer 1

我认为这只是一个微不足道的语法差异。更具体地说，我认为这是文档中的错误。8i的语法图表暗示支持任何顺序，但在8i参考文献中没有任何内容表明顺序有任何区别。但是该图表也有点暗示您可以有多个group_by_clause或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;

我的猜测是，当Oracle修复9i的语法图时，他们也忘记了顺序可以不同。或者可能是有意留下了这个问题，因为按层次结构部分先执行似乎更合理。

像这样的几个小语法变化都没有文档记录。我认为这并不意味着它们不受支持。Oracle可能后悔允许这么多奇怪的选项，并希望事情看起来至少简单一些。例如，“HAVING”可以在“GROUP BY”之前出现，许多旧的并行特性仍然有效（但被忽略），等等。（这就是为什么我总是笑人们说他们要快速“解析SQL” - 祝你好运弄清楚这个！）

Oracle 8i语法： Oracle 9i语法：

- Jon Heller

---

感谢您的研究。当然，BNF符号中，为两个子句记录顺序无关性可能会非常棘手，正如在CONNECT BY .. START WITH / START WITH .. CONNECT BY的文档中所看到的那样，这使得文档变得难以阅读。 - Lukas Eder

Answer 2

请查看执行计划。在我的环境中，它们是相同的，一个 CONNECT BY 操作馈送到 HASH GROUP BY 中。因此，将 GROUP BY 放在前面只是一种奇怪的语法，产生与更自然顺序相同的结果。

从技术上讲，这可能是解析器中的一个错误，因为正如您所说，层次查询子句应该在 group-by 子句之前。但似乎对查询的执行没有任何影响。

- Dave Costa

---

好的提示。我也认为这是解析器的一个bug（或者“隐藏功能”）。然而，这只是一个简单的查询，执行计划（甚至结果）在更复杂的查询中可能会有所不同... - Lukas Eder