借鉴duanzilin(寻) 的思路:insert into b
select "Group",max(sys_connect_by_path("Name") "Name" from
(
select "Group","Name",rn,lead(rn) over(partition by "Group" order by rn) rn1
from (
select "Group","Name",row_number() over(order by "Group","Name") rn from a
)
)
start with rn1 is null
connect by rn1 = prior rn
group by "Group"
;
select "Group",max(sys_connect_by_path("Name") "Name" from
(
select "Group","Name",rn,lead(rn) over(partition by "Group" order by rn) rn1
from (
select "Group","Name",row_number() over(order by "Group","Name") rn from a
)
)
start with rn1 is null
connect by rn1 = prior rn
group by "Group"
;
select "Group",max(sys_connect_by_path("Name") "Name" result
from (select "Group","Name",
(row_number() over(order by "Group","Name" desc)
+dense_rank() over(order by "Group")) rn,
max("Name") over(partition by "Group") name1
from a
)
start with "Name"= name1
connect by rn-1 = prior rn
group by "Group"
;
具體 的可以參考
http://www.cnoug.org/viewthread.php?tid=31313
http://www.cnoug.org/viewthread.php?tid=38387
此外,我的oracle9.2要求sys_connect_by_path有两个输入参数,而且第二个参数不允许为空。不知道上面几位的oracle版本是否也是这样的。
SYS_CONNECT_BY_PATH (column, char)
必须有2个参数,而且第2个参数不能为空