sys_connect_by_path(合并行)

--合并行的示例代码
--示例1
select id,name
from (
      select id,name,row_number() over(partition by id order by piece desc) as piece
      from (
            select id,replace(sys_connect_by_path(name,';'),';','') as name,piece
            from (
                  select id,name,piece as child,piece-1 as father,piece
                  from (
                        select 1 as id,'我是' as name,1 as piece from dual
                        union all
                        select 1 as id,'中国' as name,2 as piece from dual
                        union all
                        select 1 as id,'人' as name,3 as piece from dual
                  ) t1
            ) t2
            start with father=0
            connect by prior child=father
      ) t3
) t
where piece=1

通过sys_connect_by_path实现行的合并，通过上述代码，可举一反三，实现生成树的路径，另外，实现行的合并可使用decode函数，也可以自定义函数实现，这里只是抛砖引玉介绍sys_connect_by_path函数的方法。

注：

1.自Oracle 9i 开始，就可以通过 SYS_CONNECT_BY_PATH 函数实现将从父节点到当前行内容以“path”或者层次元素列表的形式显示出来；

--代码见顶部的示例1

2.自Oracle 10g 开始，可以利用伪列函数CONNECT_BY_ISLEAF来判断当前行是不是叶子。如 果是叶子就会在伪列中显示“1”，如果不是叶子而是一个分支就显示“0”

示例2
select id,child,father,connect_by_isleaf
from (
      select id,name,piece as child,piece-1 as father,piece
      from (
            select 1 as id,'我是' as name,1 as piece from dual
            union all
            select 1 as id,'中国' as name,2 as piece from dual
            union all
            select 1 as id,'人' as name,3 as piece from dual
      ) t1
) t2
start with father=0
connect by prior child=father

3.当然，ORACLE 10g可以根据CONNECT_BY_ROOT函数返回树的根节点。

--示例3
select connect_by_root name,id,name,child,father,piece,replace(sys_connect_by_path(name,';'),';','') as name
from (
      select id,name,piece as child,piece-1 as father,piece
      from (
            select 1 as id,'我是' as name,1 as piece from dual
            union all
            select 1 as id,'中国' as name,2 as piece from dual
            union all
            select 1 as id,'人' as name,3 as piece from dual
      ) t1
) t2
start with father=0
connect by prior child=father;
--注意:connect_by_root后必须跟上要显示的选择列，比如我这里让它显示的是name字段

--注：上述3个函数必须带上start with和connect by prior关键字