oracle connect by从句中的rownum

朱雀 2021-09-27 13:28 548阅读 0赞

在构造数据时，对于单行的基本数据来说，`connect by level`与`connect by rownum`可以说是等效的。比如说下面的两个语句的结果集是一样的。

with tab1 as ( select 'a' id, 2 num from dual ) select*from tab1 connect by level <= 2 ; with tab1 as ( select 'a' id, 2 num from dual ) select*from tab1 connect by rownum <= 2 ;

但是当基础数据为多条时，rownum会产生不符合预期的结果。

with tab1 as ( select 'a' id, 2 num from dual union all select 'b' id, 4 num from dual ) select id, num, rownum rn from tab1 connect by rownum <= 4 ;

我明明指定了rownum <= 4，却得到了5条记录，这是怎么回事？

我之后又写了多条测试语句，最终的结论是：层次查询中的rownum与最后结果集中的rownum并不是一个。

在层次查询中，rownum被赋予的是层次查询中返回的结果集的序号。第一个被查询的根节点的rownum就是1，在此基础上，下一条返回的数据为2，以此类推。当查出第四层时，查询结束。至于多出来的第五条数据，可以这样理解：connect语句的结果集由两部分组成，即由start with查询出的根节点与connect查询出的子节点组成。根节点是不受connect从句约束的，所以当rownum <= 4生效后，其他根节点一并归到了结果集中。又由于oracle的层次查询是深度优先的，所以上面的例子中，实际查出的是一颗左斜树。这就是查出4个a和1个b的原因。

用这个语句能更清晰的看出这些特点。

with tab1 as ( select 'a' id, 2 num from dual union all select 'b' id, 2 num from dual union all select 'c' id, 4 num from dual ) select id, num, rownum rn, lpad('--', level * 3, '-') || level || '_' || id path from tab1 connect by rownum <= 4

在这个语句的基础上加上where rownum <= 4确实也可以只查出4条数据。

这样可以保证只查出四条数据。

但是当语句的条件多一些后  
比如这样

with tab1 as ( select 'a' id, 2 num from dual union all select 'b' id, 2 num from dual ) select id, num, rownum rn, lpad('--', level * 3, '-') || level || '_' || id path from tab1 connect by rownum <= 6 and level <= 3 ;

这是预料之中的结果，但是我实在想不出什么样的功能会用上这种语句…