oracle group by子句,sql – GROUP BY子句之后的Oracle CONNECT BY子句

我刚刚浏览了这个

interesting article here,展示了如何使用分层查询和窗口函数在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子句之后.根据the specification,这不应该是可能的.我尝试使用简单的查询,但它确实有效！以下两个查询返回相同的结果：

-- 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;

这是一个没有文档的功能吗？或者只是语法无差异以方便？或者这两个陈述巧妙地表现得不同？