데이터베이스 질문과 답변
안녕하세요. 눈팅한지는 꽤 됐지만, 이번에 글도 처음 남기는 아무것도 모르는 신입 사원 입니다.ㅠㅠ
검색해도 잘 모르겠고, 안풀리는 부분 때문에 이렇게 염치없게 질문글을 올리게 되었습니다. 괜히 죄송스럽네요..ㅠㅠ
다름이 아니고 저희 회사 솔루션을 이용하여 현황 리스트를 구현중에, 한 컬럼에 사원번호가..
11111,22222,33333
이렇게 콤마로 구분되어 들어가 있습니다. 이걸 찢어서 사원테이블을 검색하여 이름으로 변환한 뒤, 다시 콤마로 구분하여..
홍길동,이순신,강감찬
이런식으로 보여주려 합니다. 보여주려하는 리스트의 예를 들면,
| id | title | contents | emp_name |
| id_001 | 제목 | 내용 | 홍길동,이순신,강감찬 |
| id_002 | 제목2 | 내용2 | 아이유,소녀시대,AOA |
| id_003 | 제목3 | 내용3 | 김태희,송혜교,전지현 |
이런식으로 말이죠.
그래서 짜본 쿼리가 아래와 같습니다.
SELECT t t.id
,t.title
,t.contents
,(SELECT substr(xmlagg(xmlelement(a,', '||emp_name) order by emp_name).extract('//text()'),3) emp_name
FROM employee emp, (
WITH TEMP AS(select emp_id as VAL from list_table where id='id_001')
SELECT REGEXP_SUBSTR(VAL, '[^,]+', 1, LEVEL) as emp_id
FROM TEMP
CONNECT BY LEVEL <= length(VAL) - length(replace(VAL, ',' , '')) + 1
) temp
WHERE emp.emp_id = temp.emp_id) emp_name
FROM list_table t
;
문제는, 6번째 with 절안의 where 절 부분을 넣어서 하나에 대한 내용을 뽑을때는 검색값은 나옵니다만,(해당 컬럼의 사원이름들이 모든 행에 들어가게되죠...)
그래서 조건절을 빼고 전체적으로 리스트들을 뽑아내려고 쿼리를 실행하면, 감감무소식이네요..ㅠㅜ
로컬에서 테스트로 15분을 넘게 기다려봤는데 결과 혹은 오류가 안나옵니다. ㅜㅜ
워낙에 기초가 부족한 상태라.. 고수님들께서 보시기에 어처구니 없는 쿼리라 해도, 귀엽게 봐주시고 꼭 도움 부탁드리겠습니다!!
참, 오라클은 11g 환경입니다.
감사합니다!!
by 마농
[2015.07.16 08:51:07]
(Connect By Level <= n) 를 통한 행 복제 방법은
오직 한건인 집합에 대해서만 적용해야 합니다.
여러건의 집합에 그대로 적용하면 모래시계를 만나게 될 것입니다.
http://www.gurubee.net/article/55635
by 마농
[2015.07.16 09:20:28]
WITH list_table(id, title, contents, emp_id) AS
(
SELECT 'id_001', '제목1', '내용1', '11111,22222,33333' FROM dual
UNION ALL SELECT 'id_002', '제목2', '내용2', '44444,66666,55555' FROM dual
UNION ALL SELECT 'id_003', '제목3', '내용3', '99999,88888,77777' FROM dual
)
, employee(emp_id, emp_name) AS
(
SELECT '11111', '홍길동' FROM dual
UNION ALL SELECT '22222', '이순신' FROM dual
UNION ALL SELECT '33333', '강감찬' FROM dual
UNION ALL SELECT '44444', '아이유' FROM dual
UNION ALL SELECT '55555', 'AOA' FROM dual
UNION ALL SELECT '66666', '소녀시대' FROM dual
UNION ALL SELECT '77777', '전지현' FROM dual
UNION ALL SELECT '88888', '송혜교' FROM dual
UNION ALL SELECT '99999', '김태희' FROM dual
)
SELECT t.id, t.title, t.contents
, LISTAGG(e.emp_name, ',') WITHIN GROUP(
ORDER BY INSTR(','||t.emp_id||',', ','||e.emp_id||',')) emp_name
FROM list_table t
, employee e
WHERE INSTR(','||t.emp_id||',', ','||e.emp_id||',') > 0
GROUP BY t.id, t.title, t.contents
ORDER BY id
;
by 창조의날개
[2015.07.16 10:18:15]
-- employee테이블에 emp_id로 인덱스가 잡혀 있을테니
-- 아래 처럼 하면 조금더 빠르지 않을가요?
SELECT id, title, contents
, LISTAGG((SELECT emp_name FROM employee WHERE emp_id = REGEXP_SUBSTR(list_table.emp_id, '[^,]+', 1, LV)), ',')
WITHIN GROUP(ORDER BY emp_id) emp_name
FROM list_table
, (SELECT LEVEL LV FROM DUAL CONNECT BY LEVEL <= (SELECT MAX(REGEXP_COUNT(emp_id,','))+1 FROM list_table))
WHERE REGEXP_SUBSTR(emp_id, '[^,]+', 1, LV) IS NOT NULL
GROUP BY id, title, contents
;
by 칙힝
[2015.07.16 11:01:53]
이렇게 만드는건 어떤가요 ?
이걸 함수로 만들어 놓고 사용하면 편리할 것 같네요 ㅎㅎ
SELECT wm_concat (
(SELECT 사용자명 FROM 사용자테이블 WHERE 사원번호 = txt)
)
FROM (
SELECT TRIM (REGEXP_SUBSTR ('11111,22222,33333', '[^' || ',' || ']+', 1, LEVEL)) AS txt, LEVEL
FROM dual
CONNECT BY INSTR ('11111,22222,33333', ',', 1, LEVEL - 1) > 0
)
by 손님
[2015.07.16 15:42:56]
아 정말 여러분의 답변과 지식에 정말 진심으로 감사드립니다.ㅠㅠ
헌데 list에 뽑아오려는 select 절에는 title, content,emp_name 같은 단일 항목 외에도 서브쿼리로 불러오는 항목들이 있습니다. 이런 경우에는 그룹바이 절에 넣어주질 못하는데 어찌 해결해야할까요? 모바일이라 띄어쓰기나 내용이 부실한 점. 양해 부탁 드립니다. ㅠㅠ
헌데 list에 뽑아오려는 select 절에는 title, content,emp_name 같은 단일 항목 외에도 서브쿼리로 불러오는 항목들이 있습니다. 이런 경우에는 그룹바이 절에 넣어주질 못하는데 어찌 해결해야할까요? 모바일이라 띄어쓰기나 내용이 부실한 점. 양해 부탁 드립니다. ㅠㅠ
by 손님
[2015.07.16 15:43:55]
함수는 function 말씀하시는거죠? 한번도 만들어보질 않았는데.. 이번기회에 도전해보겠습니다!
by 마농
[2015.07.16 16:06:03]
-- 1.스칼라서브쿼리를 이용할수도 있구요...
SELECT t.id, t.title, t.contents
, (SELECT LISTAGG(e.emp_name, ',') WITHIN GROUP(
ORDER BY INSTR(','||t.emp_id||',', ','||e.emp_id||','))
FROM employee e
WHERE INSTR(','||t.emp_id||',', ','||e.emp_id||',') > 0
) emp_name
FROM list_table t
;
-- 2. 별도 집합으로 조인 할 수도 있구요...
SELECT t.id, t.title, t.contents
, e.emp_name
FROM list_table t
, (SELECT t.id
, LISTAGG(e.emp_name, ',') WITHIN GROUP(
ORDER BY INSTR(','||t.emp_id||',', ','||e.emp_id||',')) emp_name
FROM list_table t
, employee e
WHERE INSTR(','||t.emp_id||',', ','||e.emp_id||',') > 0
GROUP BY t.id
) e
WHERE t.id = e.id
;
) 버튼을 클릭하여 작성 하시면 됩니다.