两字段相同时.第三字段数据合并起来,该如何实现?
条件是:字段f1,f2相同时,把字f3的内容合并.sql语句该如何实现?
示例如下:
f1f2f3
0001AAA
0002BAC
0001A AB
0003CBD
0001ABD
需要得出结果:
f1f2f3
001AAA/AB/BD
002BAC
003CBD
[解决办法]
select t.f1,
t.f2,
(select max(sys_connect_by_path(f3, '/ ')) result from
(select f1||f2 temp,f3,rn,lead(rn) over(partition by f1||f2 order by rn) rn1
from (select f1, f2, f3, row_number() over(order by f1, f2 desc) rn
from t_test )
)
start with temp = t.temp and rn1 is null connect by rn1 = prior rn
) value
from (select distinct f1, f2, f1||f2 as temp from t_test) t
---------------------------
调试过，可以用的
