with orguser as(
select '2' as userid from dual
union select '1' as userid from dual
union select '3' as userid from dual
)
select
userid,
listagg(FOLDERNAME, '/') within group(order by length(FOLDERID)) as FOLDERNAME
from (
select dl.FOLDERNAME,ud.userid,dl.FOLDERID from orgdeptlist dl right join (
select ud.userid,ud.FOLDERID from orguserdeptmap ud join orguser ui
on ui.userid = ud.userid
) ud on dl.FOLDERID in(substr(ud.FOLDERID || '', 0, 6),
substr(ud.FOLDERID || '', 0, 9),
substr(ud.FOLDERID || '', 0, 12), substr(ud.FOLDERID || '', 0, 15))
)
group by userid;
查询结果
USERID | FOLDERNAME |
---|---|
1 | xxxx/xxxx/xxx |
2 | xxxx |
2 | xxxx/xxx |
--with as 相当于一张临时表(一次分析,多次使用)
with temp as
(select 'male' as sex, 'zhangsan' as stu_name from dual
union select 'male' as sex,'lisi' as stu_name from dual
union select 'female' as sex, 'xiaohong' as stu_name from dual
union select 'female' as sex,'xiaozhang' as stu_name from dual
union select 'male' as sex,'zhaoliu' as stu_name from dual
)
---select * from temp;
--第二种方法: 使用listagg()方法,可以指定分隔的字符
select t.sex as sex,
listagg(t.stu_name, ',') within group(order by t.stu_name) as stu_name
from temp t
group by t.sex;