connect by 使用总结

hanjs

对于使用sys_connect_by_path函数来得到树型结构信息的平铺，但是使用过程中发现，有很多实现了但是查出来的数据不对。

如：
select deptno,ltrim(sys_connect_by_path(ename,','),',') path from (
    select deptno,ename,row_number() over (order by deptno) node_id,
    row_number() over (partition by deptno order by deptno) rn from emp
    )
    start with rn=1
    connect by node_id- 1 = prior node_id

1        10        CLARK
2        10        CLARK,KING
3        10        CLARK,KING,MILLER
4        20        CLARK,KING,MILLER,SMITH
5        20        CLARK,KING,MILLER,SMITH,ADAMS
6        20        CLARK,KING,MILLER,SMITH,ADAMS,FORD
7        20        CLARK,KING,MILLER,SMITH,ADAMS,FORD,SCOTT
8        20        CLARK,KING,MILLER,SMITH,ADAMS,FORD,SCOTT,JONES
9        30        CLARK,KING,MILLER,SMITH,ADAMS,FORD,SCOTT,JONES,ALLEN
10        30        CLARK,KING,MILLER,SMITH,ADAMS,FORD,SCOTT,JONES,ALLEN,BLAKE
11        30        CLARK,KING,MILLER,SMITH,ADAMS,FORD,SCOTT,JONES,ALLEN,BLAKE,MARTIN
12        30        CLARK,KING,MILLER,SMITH,ADAMS,FORD,SCOTT,JONES,ALLEN,BLAKE,MARTIN,JAMES
13        30        CLARK,KING,MILLER,SMITH,ADAMS,FORD,SCOTT,JONES,ALLEN,BLAKE,MARTIN,JAMES,TURNER
14        30        CLARK,KING,MILLER,SMITH,ADAMS,FORD,SCOTT,JONES,ALLEN,BLAKE,MARTIN,JAMES,TURNER,WARD
15        20        SMITH
16        20        SMITH,ADAMS
17        20        SMITH,ADAMS,FORD
18        20        SMITH,ADAMS,FORD,SCOTT
19        20        SMITH,ADAMS,FORD,SCOTT,JONES
20        30        SMITH,ADAMS,FORD,SCOTT,JONES,ALLEN
21        30        SMITH,ADAMS,FORD,SCOTT,JONES,ALLEN,BLAKE
22        30        SMITH,ADAMS,FORD,SCOTT,JONES,ALLEN,BLAKE,MARTIN
23        30        SMITH,ADAMS,FORD,SCOTT,JONES,ALLEN,BLAKE,MARTIN,JAMES
24        30        SMITH,ADAMS,FORD,SCOTT,JONES,ALLEN,BLAKE,MARTIN,JAMES,TURNER
25        30        SMITH,ADAMS,FORD,SCOTT,JONES,ALLEN,BLAKE,MARTIN,JAMES,TURNER,WARD
26        30        ALLEN
27        30        ALLEN,BLAKE
28        30        ALLEN,BLAKE,MARTIN
29        30        ALLEN,BLAKE,MARTIN,JAMES
30        30        ALLEN,BLAKE,MARTIN,JAMES,TURNER
31        30        ALLEN,BLAKE,MARTIN,JAMES,TURNER,WARD

通常为了满足结果，我们都取最大的，可是问题出现了。

select deptno,ltrim(max(sys_connect_by_path(ename,',')),',') path from (
    select deptno,ename,row_number() over (order by deptno) node_id,
    row_number() over (partition by deptno order by deptno) rn from emp
    )
    start with rn=1
    connect by node_id- 1 = prior node_id
    group by deptno

1        10        CLARK,KING,MILLER
2        20        SMITH,ADAMS,FORD,SCOTT,JONES
3        30        SMITH,ADAMS,FORD,SCOTT,JONES,ALLEN,BLAKE,MARTIN,JAMES,TURNER,WARD

请看部门30的雇员名称，多了其他部门的人，为什么呢。
因为我们构建连接的条件是node_id- 1 = prior node_id，看下连接前的信息。

select deptno,ename,row_number() over (order by deptno) node_id,
    row_number() over (partition by deptno order by deptno) rn from emp

1        10        CLARK        1        1
2        10        KING        2        2
3        10        MILLER        3        3
4        20        SMITH        4        1
5        20        ADAMS        5        2
6        20        FORD        6        3
7        20        SCOTT        7        4
8        20        JONES        8        5
9        30        ALLEN        9        1
10        30        BLAKE        10        2
11        30        MARTIN        11        3
12        30        JAMES        12        4
13        30        TURNER        13        5
14        30        WARD        14        6


这样，第一个部门10，遍历10、20、30的记录，部门20遍历20、30的记录，部门30遍历30自己的记录，这样再求最大的时候，可能就会出错了。

解决办法是：
select deptno,ltrim(max(sys_connect_by_path(ename,',')),',') path from (
    select deptno,ename,2*row_number() over (order by deptno) - row_number() over (partition by deptno order by deptno) node_id,
    row_number() over (partition by deptno order by deptno) rn from emp
    )
    start with rn=1
    connect by node_id- 1 = prior node_id
    group by deptno

注意上面的2*row_number() over (order by deptno) - row_number() over (partition by deptno order by deptno) node_id，这样就分隔了各个部门的数据联系了。看下连接前的信息

1        10        CLARK        1        1
2        10        KING        2        2
3        10        MILLER        3        3
4        20        SMITH        7        1
5        20        ADAMS        8        2
6        20        FORD        9        3
7        20        SCOTT        10        4
8        20        JONES        11        5
9        30        ALLEN        17        1
10        30        BLAKE        18        2
11        30        MARTIN        19        3
12        30        JAMES        20        4
13        30        TURNER        21        5
14        30        WARD        22        6

各个部门之间的数据是断开的，而且各部门内也能满足连接条件node_id- 1 = prior node_id。这样处理后查询处理的信息如下：

1        10        CLARK
2        10        CLARK,KING
3        10        CLARK,KING,MILLER
4        20        SMITH
5        20        SMITH,ADAMS
6        20        SMITH,ADAMS,FORD
7        20        SMITH,ADAMS,FORD,SCOTT
8        20        SMITH,ADAMS,FORD,SCOTT,JONES
9        30        ALLEN
10        30        ALLEN,BLAKE
11        30        ALLEN,BLAKE,MARTIN
12        30        ALLEN,BLAKE,MARTIN,JAMES
13        30        ALLEN,BLAKE,MARTIN,JAMES,TURNER
14        30        ALLEN,BLAKE,MARTIN,JAMES,TURNER,WARD

emp表一共14条记录，这样连接出来的也是14条，而上面的连接出现了31条。这样统一的处理方法就是上面的一个小小的技巧了。

希望对大家能有帮助！

nyfor

其实只要在Connect By子句中增加一个部门的条件就可以了。
connect by node_id - 1 = prior node_id and deptno = prior deptno

freeka

学习，我可以收藏转贴么

hanjs

最初由 nyfor 发布
[B]其实只要在Connect By子句中增加一个部门的条件就可以了。
connect by node_id - 1 = prior node_id and deptno = prior deptno [/B]

学习了！

skymysky777

LZ挺强。。。。。。。。。
俺慢慢学习一下。

hanjs

最初由 freeka 发布
[B]学习，我可以收藏转贴么 [/B]

java不是都讲究开源么？可以转啊

说明出处就好了！

hanjs

学习了！

cyr1974

select deptno, ltrim(sys_connect_by_path(ename, ','), ',') path
  from (select deptno,
               ename,
               2 * row_number() over(order by deptno) - row_number() over(partition by deptno order by deptno) node_id,
               row_number() over(partition by deptno order by deptno) rn
          from emp)
start with rn = 1
connect by node_id - 1 = prior node_id
group by deptno
这样做不行吧

hanjs

最初由 cyr1974 发布
[B]select deptno, ltrim(sys_connect_by_path(ename, ','), ',') path
  from (select deptno,
               ename,
               2 * row_number() over(order by deptno) - row_number() over(partition by deptno order by deptno) node_id,
               row_number() over(partition by deptno order by deptno) rn
          from emp)
start with rn = 1
connect by node_id - 1 = prior node_id
group by deptno
这样做不行吧 [/B]

缺少个max函数，已更改，多谢指正！