데이터베이스 질문과 답변
| NAME | S_POS | U_SIZE | U_LITMIT |
| A-5 | 28,32,31,30 | 1,1,1,1 | 47 |
| A-6 | 3,23 | 20,1 | 47 |
| B-3 | 4,13 | 8,1 | 45 |
| B-4 | 8,36,32,29,24,20,12 | 4,4,4,1,4,4,4 | 45 |
| B-5 | 5,39,20,17,14,11,8 | 2,2,2,2,2,2,2 | 41 |
이런 데이터가 있을때에..
A-5 라는 장비의 최대 적재 용량은 47 유닛이고
각 장비는 28, 32, 31, 30 번째에 적재 되어있으며, 각각의 크기는 1,1,1,1 입니다.
이 데이터를 가지고
A-5라는 장비의 최대 47칸중에 빈칸을 알아내려고할때
SELECT LEVEL FROM DUAL CONNECT BY LEVEL <= 47; 이 쿼리와 MINUS를 써서 각각은 조회를 했었는데요..
저런식으로 여러 ROW의 데이터에 대해서 알아낼수있을까요?
음... 질문은 대충 이해가 가긴 하는데... 결과 예시를 올려주시는 것이 명확할 것 같습니다.
아! 죄송합니다.
원하는 출력 결과는..
A-5를 예를 들 경우
1,2,3,4,5,6,7,8,10 ....... 41,42,43,44,45,46,47
중에서
28,30,31,32를 제거한
1,2,3,.....27,29,33,34,35......46,47 이런 데이터가 출력되길 원하고있습니다.
A-6의 경우에는
1,2,3,4,5,6,7,8,10 ....... 41,42,43,44,45,46,47
중에서
3, 23 인데 3의 경우 3+(20-1) > 3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22 (3부터 연속된 20개의 숫자)
23의 경우 23+(1-1) > 23
1,2,24,25,26,27.....44,45,46,47
위와 같은 결과가 출력되길 원합니다. 가능할까요?..
with t as
(
select 'A-5' name, '28,32,31,30' s_pos, '1,1,1,1' u_size, 47 u_limit from dual
union all
select 'A-6' name, '3,23' s_pos, '20,1' u_size, 47 u_limit from dual
union all
select 'B-3' name, '4,13' s_pos, '8,1' u_size, 45 u_limit from dual
)
select name, regexp_replace(replace((regexp_replace(allist,''''||replace(s_pos,',','''|''')||'''','')),'''',''),',+',',') result
from
(
select name, s_pos, u_size
, (select listagg(''''||to_char(level)||'''',',') within group(order by level)
from dual
connect by level <= u_limit) allist
from t
)
저도 아직 배우는 입장이라.. 어설프지만 이런 식으로 생각해봤습니다.
답변 감사합니다 jkson 님이 주신 쿼리로 적용해보니
각각의 name별로 1개씩은 제거가 되는데
각 name별로 차지하지 크기가 달라서 A-6의 3번 같은 경우 3부터 22까지의 숫자를 제거해야합니다.
도움주신 방법으로 한번 해봐야겠습니다. 감사합니다 ^^
with t as
(
select 'A-5' name, '28,32,31,30' s_pos, '1,1,1,1' u_size, 47 u_limit from dual
union all
select 'A-6' name, '3,23' s_pos, '20,1' u_size, 47 u_limit from dual
union all
select 'B-3' name, '4,13' s_pos, '8,1' u_size, 45 u_limit from dual
)
select name, regexp_replace(replace((regexp_replace(allist,''''||replace(s_pos,',','''|''')||'''','')),'''',''),',+',',') result
from
(
select a.name, b.pos s_pos, u_size
, (select listagg(''''||to_char(level)||'''',',') within group(order by level)
from dual
connect by level <= u_limit) allist
from t a,
(select name, listagg ((select listagg(to_char(pos+level-1),',') within group(order by level)
from dual
connect by level <= len),',') within group(order by pos) pos
from
(
select name, lv, regexp_substr(s_pos,'[^,]+',1,lv) pos , regexp_substr(u_size,'[^,]+',1,lv)len from t, (select level lv from dual connect by level <= 47) x
)
where pos + len > 0
group by name
) b
where a.name = b.name
)
아.. 크기 조건도 봐야하는군요.. 저는 아직 실력이 모자라서 쿼리가 점점 길어지네요;;
다른 고수님께서 짧고 간단한 방법으로 답글 달아주시길 바랍니다^^;
우왕... = ㅅ =) 잘 짜셨네요. 배우고 갑니다.
쉽지 않네요.
와... 감사합니다!! 한수 배우고갑니다~!!
WITH t AS ( SELECT 'A-5' name, '28,32,31,30' s_pos, '1,1,1,1' u_size, 47 u_litmit FROM dual UNION ALL SELECT 'A-6', '3,23' , '20,1' , 47 FROM dual UNION ALL SELECT 'B-3', '4,13' , '8,1' , 45 FROM dual UNION ALL SELECT 'B-4', '8,36,32,29,24,20,12', '4,4,4,1,4,4,4', 45 FROM dual UNION ALL SELECT 'B-5', '5,39,20,17,14,11,8' , '2,2,2,2,2,2,2', 41 FROM dual ) SELECT name, s_pos, u_size, u_litmit , LISTAGG(a.z, ',') WITHIN GROUP(ORDER BY a.z) z FROM (SELECT LEVEL z FROM dual CONNECT BY LEVEL <= 99) a LEFT OUTER JOIN (SELECT name, s_pos, u_size, u_litmit , REGEXP_SUBSTR(s_pos, '[^,]+', 1, x) + y - 1 z FROM t , (SELECT LEVEL x FROM dual CONNECT BY LEVEL <= 99) , (SELECT LEVEL y FROM dual CONNECT BY LEVEL <= 99) WHERE x <= REGEXP_COUNT(s_pos, ',')+1 AND y <= REGEXP_SUBSTR(u_size, '[^,]+', 1, x) ORDER BY name, x, y ) b PARTITION BY (name, s_pos, u_size, u_litmit) ON (a.z = b.z) WHERE b.z IS NULL AND a.z <= u_litmit GROUP BY name, s_pos, u_size, u_litmit ;
WITH t AS
(
SELECT 'A-5' name, '28,32,31,30' s_pos, '1,1,1,1' u_size, 47 u_limit FROM dual
UNION ALL SELECT 'A-6', '3,23' , '20,1' , 47 FROM dual
UNION ALL SELECT 'B-3', '4,13' , '8,1' , 45 FROM dual
UNION ALL SELECT 'B-4', '8,36,32,29,24,20,12', '4,4,4,1,4,4,4', 45 FROM dual
UNION ALL SELECT 'B-5', '5,39,20,17,14,11,8' , '2,2,2,2,2,2,2', 41 FROM dual
)
select name,s_pos,u_size,u_limit,listagg(lv,',') within group(order by lv) aa
from t, (select level lv from dual connect by level <=47)
where t.u_limit >=lv
and (name,lv) not in (
select name,u_limit
from (
select distinct name,s_pos,u_size
,to_number(regexp_substr(s_pos,'[^,]+',1,s_gb)) fr
,regexp_substr(s_pos,'[^,]+',1,s_gb) + (regexp_substr(u_size,'[^,]+',1,u_gb) - 1) to_
from (
select name,s_pos,u_size
,mod(lv-1,(regexp_count(s_pos,',') +1)) +1 s_gb
,mod(lv-1,(regexp_count(u_size,',') +1)) +1 u_gb
,lv
from t a, (select level lv from dual connect by level <= 10) b
where a.u_limit >= lv)) aa,(select level u_limit from dual connect by level <=47) b
where u_limit between fr and to_)
group by name,s_pos,u_size,u_limit;
) 버튼을 클릭하여 작성 하시면 됩니다.