SQL_logics/windowfunction at master · LeonLipu/SQL_logics · GitHub

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43

with a as (

select 'CEO' role, 'None' report from dual
union all
select 'M1' role, 'CEO' report from dual
union all
select 'M2' role, 'CEO' report from dual
union all
select 'R1' role, 'M1' report from dual
union all
select 'R2' role, 'M1' report from dual
union all
select 'R3' role, 'M2' report from dual
union all
select 'R4' role, 'M2' report from dual
union all
select 'R5' role, 'M2' report from dual

)

select sys_connect_by_path(role,'/') path ,role,report from a start with role='R1' connect by prior report=role

PATH	ROLE	REPORT
/R1	R1	M1
/R1/M1	M1	CEO
/R1/M1/CEO	CEO	None


with k as (select 'w1' as word,'s1,s2,s3' as syn from dual union all select 'w2' as word , 'e1,e2,e3' as syn from dual  )

select word,regexp_substr(syn,'[^,]+',1,level) from k connect by regexp_instr(syn,'[^,]+',1,level)>0  and prior sys_guid() is not null and prior word=word


WORD	REGEXP_SUBSTR(SYN,'[^,]+',1,LEVEL)
w1	s1
w1	s2
w1	s3
w2	e1
w2	e2
w2	e3