原表id name
-----------
aa high1
bb high1
cc high2
dd high3
ee high3
ff high3
...
目标id name
-----------------
aabb high1
cc high2
ddeeff high3
程序如下:
select replace(MAX(SYS_CONNECT_BY_PATH(id, ',')), ',', '') id,
name
from (select id,
name,
row_number() over(partition by name order by rownum) rn
from test)
START WITH rn = 1
CONNECT BY PRIOR rn + 1 = rn
AND PRIOR name = name
group by name;
name
from (select id,
name,
row_number() over(partition by name order by rownum) rn
from test)
START WITH rn = 1
CONNECT BY PRIOR rn + 1 = rn
AND PRIOR name = name
group by name;
START WITH 代表你要开始遍历的的节点,
CONNECT BY PRIOR 是标示父子关系的对应!
CONNECT BY PRIOR 是标示父子关系的对应!
其实SYS_CONNECT_BY_PATH这个函数是oracle9i才新提出来的!
它一定要和connect by子句合用!
第一个参数是形成树形式的字段,第二个参数是父级和其子级分隔显示用的分隔符!
说白了这个函数的作用就是为你清楚的构建出树结构的查询结果。
[@more@]
它一定要和connect by子句合用!
第一个参数是形成树形式的字段,第二个参数是父级和其子级分隔显示用的分隔符!
说白了这个函数的作用就是为你清楚的构建出树结构的查询结果。
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/9567544/viewspace-989106/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/9567544/viewspace-989106/