데이터베이스 질문과 답변
안녕하세요.
아래와 같은 결과,결과2 쿼리를 뽑을 수 있을까요?
결과 : a테이블의 코드와 b테이블의 코드가 같은 것들에 대해서 칼럼을 추가( b의 지역), 없다면 빈 값.
결과2: a테이블의 코드와 b테이블의 코드가 같은 것들에 대해서 동일한 지역에 대해서 값을 삭제.
도움부탁드립니다!
a
| 코드 | 국가 |
| no1 | 한국,미국,일본 |
| no2 | 한국,중국 |
b
| 코드 | 지역 |
| no1 | 한국 |
| no1 | 미국 |
결과
| 코드 | 국가 | 지역 |
| no1 | 한국,미국,일본 | 한국,미국 |
| no2 | 한국,중국 |
결과2
| 코드 | 국가 |
| no1 | 일본 |
| no2 | 한국,중국 |
by jkson
[2017.08.17 09:30:54]
--첫 번째
with t1
as (
select 'no1' as cd,'한국,미국,일본' as nt from dual union all
select 'no2','한국,중국' from dual
)
, t2
as (
select 'no1' as cd,'한국' as rg from dual union all
select 'no1','미국' from dual
)
select a.cd, a.nt
, listagg(b.rg,',') within group(order by b.cd) rg
from t1 a , t2 b
where a.cd = b.cd(+)
group by a.cd, a.nt
--두 번째
with t1
as (
select 'no1' as cd,'한국,미국,일본' as nt from dual union all
select 'no2','한국,중국' from dual
)
, t2
as (
select 'no1' as cd,'한국' as rg from dual union all
select 'no1','미국' from dual
)
select cd
, listagg(rg,',') within group(order by lv) rg
from
(
select cd, regexp_substr(nt,'[^,]+',1,lv) rg, lv
from t1
, (select level lv from dual connect by level < 20)
where regexp_count(nt,',') >= lv - 1
) a
where not exists (select 1
from t2 b
where b.cd = a.cd
and rg = a.rg)
group by cd
--두 번째 다른 방법
select a.cd
, trim(both ',' from regexp_replace(regexp_replace(a.nt,b.rg),',+',',')) rg
from t1 a,
(
select cd, listagg(rg,'|') within group(order by rg) rg
from t2
group by cd
) b
where a.cd = b.cd(+)
by 구루업투
[2017.08.17 09:58:11]
감사합니다!
by 우리집아찌
[2017.08.17 09:40:17]
WITH A AS (
SELECT 'NO1' CD , '한국,미국,일본' COUNTRY FROM DUAL UNION ALL
SELECT 'N02' , '한국,중국' FROM DUAL
) , B AS (
SELECT 'NO1' CD , '한국' AREA FROM DUAL UNION ALL
SELECT 'NO1' , '미국' FROM DUAL
)
SELECT A.CD , A.COUNTRY
, LISTAGG(AREA , ',') WITHIN GROUP(ORDER BY AREA DESC) AREA1
, LTRIM(REPLACE(A.COUNTRY ,LISTAGG(AREA , ',') WITHIN GROUP(ORDER BY AREA DESC) ,'') ,',')AEREA2
FROM A
, B
WHERE A.CD = B.CD(+)
GROUP BY A.CD , A.COUNTRY
ORDER BY A.CD DESC
by jkson
[2017.08.17 09:49:50]
a의 국가가 항상 역순으로 소트되어있다는 전제하에 두 번째가 작동하겠네요~
항상 소트된 상태가 맞다고 한다면 이 쿼리가 더 좋을듯해요~~
by 우리집아찌
[2017.08.17 09:54:48]
아.. 중간에 딴게 들어갈수 있구낭..
비몽사몽에 짰떠니
by 구루업투
[2017.08.17 09:58:26]
감사합니다! 오늘도 배우고갑니다 ^^
by 마농
[2017.08.17 10:00:31]
SELECT cd, nt
, REPLACE(rg, '|') v1
, SUBSTR(REGEXP_REPLACE(','||nt, NVL2(rg, ','||rg, '')), 2) v2
FROM (SELECT a.cd, a.nt
, LISTAGG(b.rg, '|,') WITHIN GROUP(ORDER BY INSTR(a.nt, b.rg)) rg
FROM t1 a
, t2 b
WHERE a.cd = b.cd(+)
GROUP BY a.cd, a.nt
ORDER BY cd
)
;
by jkson
[2017.08.17 10:27:53]
콤마까지 묶어주니까 훨씬 간단하네요~
by 우리집아찌
[2017.08.17 10:41:10]
WITH A AS (
SELECT 'NO1' CD , '독일,한국,미국,일본' COUNTRY FROM DUAL UNION ALL
SELECT 'N02' , '한국,중국' FROM DUAL
) , B AS (
SELECT 'NO1' CD , '한국' AREA FROM DUAL UNION ALL
SELECT 'NO1' , '미국' FROM DUAL
)
SELECT cd
, RTRIM(LTRIM(
REGEXP_REPLACE(REGEXP_REPLACE( COUNTRY , AREA ,''),'[,]+' , ',')
,','),',') v1
FROM
(
SELECT A.CD, A.COUNTRY
, LISTAGG(B.AREA , '|') WITHIN GROUP(ORDER BY B.AREA ) AREA
FROM A
, B
WHERE A.CD = B.CD(+)
GROUP BY A.cd, A.COUNTRY
ORDER BY A.CD DESC
)
) 버튼을 클릭하여 작성 하시면 됩니다.