{1,2,3}
이 집합으로 주어진 경우
(3),(2),(2,3),(3,2),(1),(1,3),(3,1),(1,2),(2,1),(1,2,3),(2,1,3),(1,3,2),(3,2,1)
을 구하는 것입니다.
자세히 보면 공집합을 제외한 멱집합의 전 순서 순서쌍을 나열한 것입니다.
결과값에 {2,3,1}, {3,1,2}가 빠졌네요.
with base_data as (
select '1,2,3' v from dual
)
, temp as (
select regexp_substr(t1.v, '[^,]+', 1, level) v
from base_data t1
connect by level <= length(regexp_replace(t1.v, '[^,]+', ''))+1
select substr(sys_connect_by_path(v, '-'), 2) as path
from temp x
connect by nocycle prior x.v <> x.v
order siblings by v
아 정말 빠졌네요
예 어쨋든 앞의 두 문제의 합성입니다.
제가 생각했던 답입니다.
앞의 두 문제를 합성하였습니다.
with base_data as
(
select 1 v from dual
union all select 2 v from dual
union all select 3 v from dual
, base_calc as
select
rownum 부분집합번호
, sys_connect_by_path(v, ',') v
, length(sys_connect_by_path(v, ',')) - length(replace(sys_connect_by_path(v, ','),',','') ) len
from base_data
connect by prior v < v
, base_recur ( v , len , tar, tarlen,pos ) as
a.v
,a.len
,substr( a.v , 1 ,nvl(nullif(instr( a.v , ',' , 1,2 ),0),length(a.v)+1) - 1 ) tar
, 2 tarlen
, 2 pos
from base_calc a
union all
,substr(a.tar,1, nvl(nullif(instr(a.tar,',',1,b.column_value),0),length(a.tar)+1) - 1)||substr(a.v,instr(a.v,',',1,a.pos) , nvl(nullif(instr(a.v,',',1,a.pos+1),0),length(a.v)+1) - instr(a.v,',',1,a.pos) )||substr(a.tar,nvl(nullif(instr(a.tar,',',1,b.column_value),0),length(a.tar) + 1))
,a.tarlen + 1
,a.pos + 1
from base_recur a
, TABLE(select collect(level) from dual connect by level<= a.tarlen) b
where a.pos <= a.len
substr(a.tar,2) tar
where a.len + 1 = a.pos
/
그런데 팡님의 방법이 훨씬 간결하고 좋습니다.
