이관테이블의 구조가 어찌 되는지? 정확한 요구사항은 어찌 되는지?
1. (순번 + 게시판구분) 또는 (게시판구분 + 순번) 으로 복합키를 구성하시면 중복은 안날 듯 합니다.
Connect By 조건 주실때도 두개 키컬럼 모두를 조건으로 주시면 됩니다.
CONNECT BY PRIOR 순번 = 부모순번
AND PRIOR 게시판구분 = 게시판구분
;
2. (게시판구분 || 순번) 의 인조키를 만드는 방법
- 'A0001', 'B0001'
3. 순번은 기존 형식을 유지하면서 일자순서대로 중복 없이 부여해야만 하는 경우
- Union ALl
- Row_number 로 순번 재 부여
- 부모순번은 위에서 만든 순번을 참조
- 조회 결과 이관
-- 위 3번 방법에 대한 예시입니다. -- WITH bbs_01 AS ( SELECT 1 seq, 0 p_seq, '20130301' dt, '01-1' title FROM dual UNION ALL SELECT 2, 1, '20130302', '01-1-2' FROM dual UNION ALL SELECT 3, 1, '20130303', '01-1-3' FROM dual UNION ALL SELECT 4, 0, '20130304', '01-4' FROM dual UNION ALL SELECT 5, 0, '20130305', '01-5' FROM dual ) , bbs_02 AS ( SELECT 1 seq, 0 p_seq, '20130302' dt, '02-1' title FROM dual UNION ALL SELECT 2, 1, '20130302', '02-1-2' FROM dual UNION ALL SELECT 3, 1, '20130303', '02-1-3' FROM dual UNION ALL SELECT 4, 0, '20130303', '02-4' FROM dual UNION ALL SELECT 5, 0, '20130308', '02-5' FROM dual ) , bbs_03 AS ( SELECT 1 seq, 0 p_seq, '20130306' dt, '03-1' title FROM dual UNION ALL SELECT 2, 1, '20130306', '03-1-2' FROM dual UNION ALL SELECT 3, 1, '20130307', '03-1-3' FROM dual UNION ALL SELECT 4, 0, '20130307', '03-4' FROM dual UNION ALL SELECT 5, 0, '20130309', '03-5' FROM dual ) , bbs_sum AS ( -- 유니온 하여 순번 부여 -- SELECT gb, seq, p_seq, dt, title , ROW_NUMBER() OVER(ORDER BY dt, seq) seq_new FROM (SELECT '01' gb, seq, p_seq, dt, title FROM bbs_01 UNION ALL SELECT '02' gb, seq, p_seq, dt, title FROM bbs_02 UNION ALL SELECT '03' gb, seq, p_seq, dt, title FROM bbs_03 ) ) , bbs_mig AS ( -- 여기까지가 Data Migration -- SELECT a.gb , a.seq seq_old , a.p_seq p_seq_old , a.dt , a.title , a.seq_new , NVL(b.seq_new, 0) p_seq_new FROM bbs_sum a , bbs_sum b WHERE a.gb = b.gb(+) AND a.p_seq = b.seq(+) --ORDER BY seq_new ) -- 여기는 Mig 후에 계층 쿼리 -- SELECT * FROM bbs_mig START WITH p_seq_new = 0 CONNECT BY PRIOR seq_new = p_seq_new ORDER SIBLINGS BY seq_new * DECODE(p_seq_new, 0, -1, 1) ;
-- 수정1 : 최상위 글은 번호와 부모번호가 같은걸로 다시 작성 -- -- 위 3번 방법에 대한 예시입니다. -- WITH bbs_01 AS ( SELECT 1 seq, 1 p_seq, '20130301' dt, '01-1' title FROM dual UNION ALL SELECT 2, 1, '20130302', '01-1-2' FROM dual UNION ALL SELECT 3, 1, '20130303', '01-1-3' FROM dual UNION ALL SELECT 4, 4, '20130304', '01-4' FROM dual UNION ALL SELECT 5, 5, '20130305', '01-5' FROM dual ) , bbs_02 AS ( SELECT 1 seq, 1 p_seq, '20130302' dt, '02-1' title FROM dual UNION ALL SELECT 2, 1, '20130302', '02-1-2' FROM dual UNION ALL SELECT 3, 1, '20130303', '02-1-3' FROM dual UNION ALL SELECT 4, 4, '20130303', '02-4' FROM dual UNION ALL SELECT 5, 5, '20130308', '02-5' FROM dual ) , bbs_03 AS ( SELECT 1 seq, 1 p_seq, '20130306' dt, '03-1' title FROM dual UNION ALL SELECT 2, 1, '20130306', '03-1-2' FROM dual UNION ALL SELECT 3, 1, '20130307', '03-1-3' FROM dual UNION ALL SELECT 4, 4, '20130307', '03-4' FROM dual UNION ALL SELECT 5, 5, '20130309', '03-5' FROM dual ) , bbs_sum AS ( -- 유니온 하여 순번 부여 -- SELECT gb, seq, p_seq, dt, title , ROW_NUMBER() OVER(ORDER BY dt, seq) seq_new FROM (SELECT '01' gb, seq, p_seq, dt, title FROM bbs_01 UNION ALL SELECT '02' gb, seq, p_seq, dt, title FROM bbs_02 UNION ALL SELECT '03' gb, seq, p_seq, dt, title FROM bbs_03 ) ) , bbs_mig AS ( -- 여기까지가 Data Migration -- SELECT a.gb , a.seq seq_old , a.p_seq p_seq_old , a.dt , a.title , a.seq_new , b.seq_new p_seq_new FROM bbs_sum a , bbs_sum b WHERE a.gb = b.gb AND a.p_seq = b.seq -- ORDER BY seq_new ) -- 여기는 Mig 후에 계층 쿼리 -- SELECT * FROM bbs_mig START WITH p_seq_new = seq_new CONNECT BY PRIOR seq_new = NULLIF(p_seq_new, seq_new) ORDER SIBLINGS BY seq_new * DECODE(p_seq_new, seq_new, -1, 1) ;