对于使用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自己的记录，这样再求最大的时候，可能就会出错了。这样就需要增加一个连接条件了：connect by node_id- 1 = prior node_id and deptno = prior deptno就可以了。如果不增加这个条件也可以，见下面处理


解决办法是：
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条。这样统一的处理方法就是上面的一个小小的技巧了。

希望对大家能有帮助！